Chapter 1 JDBC overview
Before that, we learned JavaSE and wrote Java programs. The data is stored in variables, arrays, sets, etc., which cannot be persisted. Later, we learned that IO streams can write data to files, but it is not convenient to manage data and maintain the relationship between data;
Later, we learned the database management software MySQL, which can easily manage data.
So how to combine them? That is, the Java program < = = > MySQL realizes the storage and processing of data.
Then you can use JDBC technology.
1.1 JDBC overview
JDBC: Java Database Connectivity, which represents a set of API s independent of any database management system (DBMS), which is declared in Java SQL and javax SQL package is a set of interface specifications provided by sun (now Oracle). Implementation classes are provided by various database manufacturers, and the collection of these implementation classes constitutes a database driven jar.
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-lIZxc2GI-1645886343690)(imgs/1561212287381.png)]
That is, JDBC technology includes two parts:
(1)java.sql package and javax API in SQL package
For the portability and maintainability of the project code, SUN company has formulated a unified interface specification for Java programs to connect various databases from the beginning. In this way, no matter which DBMS software is connected, the Java code can be consistent.
(2) Jars provided by various database manufacturers
Because the DBMS software of each database manufacturer is different, only the database manufacturer knows how to add, delete, modify, query and other management data through sql. Therefore, the implementation of the interface specification is handed over to each database manufacturer.
1.2 JDBC usage steps
Code writing steps:
1. Register driver
trilogy:
(1) Copy the driver jar of DBMS database management software to the libs directory of the project
For example: mysql-connector-java-5.1.36-bin.com jar
(2) Add the driver jar to the build path of the project
(3) Load driver class into memory
Class.forName("com.mysql.jdbc.Driver");
2. Get Connection object
Connection conn = DriverManager.getConnection(url,username,password);
MySQL url: jdbc:mysql://localhost:3306/ Database name? Parameter name = parameter value
jdbc: mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8 (if the JDBC program is inconsistent with the character set on the server side, which will cause garbled code, you can specify the character set on the server side through parameters
3. Execute sql and process the results
(1) Write sql
(2) Create a Statement or PreparedStatement object
(3) Execute sql
Addition, deletion and modification: call the executeupdate method
Query: call executeQuery method
(4) Processing results
Add, delete and modify: returns an integer value
Query: return the ResultSet result, which needs to be traversed with the combination of next() and getXxx()
4. Release connection, etc
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-nhgcmlth-164588634692) (IMGs / 1561213163143. PNG)]
Related API s:
1. DriverManager: driver management class
2. Connection: represents a database connection
3. Statement and PreparedStatement: used to execute sql
Execute addition, deletion and modification: int executeupdate()
Execute query: ResultSet executeQuery()
4. How to traverse the ResultSet?
(1) boolean next(): judge whether there is another line
(2) getString (field name or sequence number), getInt (field name or sequence number), GetObject (field name or sequence number)
Example code 1: add, delete and modify
public class TestJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. Register driver
//(1) Method 1: class Forname ("full name of driver class")
Class.forName("com.mysql.jdbc.Driver");
// (2) Create an object that drives the class
// new com.mysql.jdbc.Driver();// Hard coding
//(3) Register drivers through DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());// Hard coding
//2. Get connection, connect to database
//TCP/IP protocol programming requires the IP address and port number of the server
//mysql url format: jdbc protocol: sub protocol: / / host name: port number / database name to connect
String url = "jdbc:mysql://localhost:3306/test "; / / where test is the database name
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
//3. Execute sql
//Add a department to the database_ Department table
//(1) Write sql
String sql = "insert into t_department values(null,'Calculation unit 2','Calculate banknote 2')";
/*
* memory: TCP/IP program error
* Socket Representative connection
* socket.getOutputStream()To send data,
* socket.getInputStream()To receive data
*
* You can compare Connection to Socket
* Compare Statement to OutputStream
*/
//(2) Get Statement object
Statement st = conn.createStatement();
//(3) Execute sql
int len = st.executeUpdate(sql);
//(4) Processing results
System.out.println(len>0?"success":"fail");
//4. Shut down
st.close();
conn.close();
}
}
Example code 2: query
public class TestSelect {
public static void main(String[] args) throws Exception{
// 1. Register driver
Class.forName("com.mysql.jdbc.Driver");
// 2. Connect database
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
// 3. Execute sql
String sql = "SELECT * FROM t_department";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);//ResultSet InputStream
while(rs.next()){//next() indicates whether there is a next line
Object did = rs.getObject(1);//Gets the value of column n
Object dname = rs.getObject(2);
Object desc = rs.getObject(3);
/*
int did = rs.getInt("did");//It can also be obtained by column name and data type
String dname = rs.getString("dname");
String desc = rs.getString("description");
*/
System.out.println(did +"\t" + dname + "\t"+ desc);
}
// 4. Shut down
rs.close();
st.close();
conn.close();
}
}
The second chapter deals with CRUD using PreparedStatement
2.1 solve the problem of Statement through PreparedStatement
Statement problem: replace it with PreparedStatement
(1) sql splicing
String sql = "insert into t_employee(ename,tel,gender,salary) values('" + ename + "','" + tel + "','" + gender + "'," + salary +")";
Statement st = conn.createStatement();
int len = st.executeUpdate(sql);
(2) sql injection
String sql = "SELECT * FROM t_employee where ename='" + ename + "'"; //If I input the ename value from the keyboard at this time, enter: Zhang San 'or' 1 '=' 1 //The result will find out all the data Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql);
(3) Processing blob and other types of data
String sql = "insert into user(username,photo) values('chailinyan', Picture byte stream)";
//At this time, when photo is blob type data, it cannot be directly spliced in sql
PreparedStatement solves the problem:
(1) Avoid sql splicing
String sql = "insert into t_employee(ename,tel,gender,salary) values(?,?,?,?)"; PreparedStatement pst = conn.prepareStatement(sql);//Do you want a tape here? And then the mysql side will precompile the sql //set up? Specific value of /*pst.setString(1, ename); pst.setString(2, tel); pst.setString(3, gender); pst.setDouble(4, salary);*/ pst.setObject(1, ename); pst.setObject(2, tel); pst.setObject(3, gender); pst.setObject(4, salary); int len = pst.executeUpdate();//sql cannot be transferred here System.out.println(len);
(2) No sql injection
String sql = "SELECT * FROM t_employee where ename=?"; //Even if you enter 'Zhang San' or '1'= '1', there is no problem PreparedStatement pst = conn.prepareStatement(sql); //Intermediate join settings? Value of pst.setObject(1, ename); ResultSet rs = pst.executeQuery();
(3) Processing blob type data
String sql = "insert into user(username,photo) values(?,?)";
PreparedStatement pst = conn.prepareStatement(sql);
//set up? Value of
pst.setObject(1, "chailinyan");
FileInputStream fis = new FileInputStream("D:/QMDownload/img/beauty/15.jpg");
pst.setBlob(2, fis);
int len = pst.executeUpdate();
System.out.println(len>0?"success":"fail");
-
Note two questions:
①my.ini has a size limit on the uploaded byte stream file, which can be found in my Ini configuration variables
max_allowed_packet=16M
② Each blob has its own size limit:
tinyblob:255 bytes, blob:65k, mediumblob:16M, longblob:4G
2.2 get self growth key value
/*
* We add a record to the database table through JDBC, in which a field is self incremented. How can we directly obtain the self incremented value after adding in JDBC
* PreparedStatement Is a sub interface of the Statement.
* Statement There are some constant values in the interface:
* (1)Statement.RETURN_GENERATED_KEYS
*
* To add and then get the self incremented key value:
* (1)PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
* (2)After adding sql, call getGeneratedKeys() method through the object of PreparedStatement to obtain the self growth key value and traverse the result set
* ResultSet rs = pst.getGeneratedKeys();
*/
public class TestAutoIncrement {
public static void main(String[] args) throws Exception{
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
//3. Execute sql
String sql = "insert into t_department values(null,?,?)";
/*
* Here, when creating the PreparedStatement object, the function of passing in the second parameter is to inform the server
* After executing the sql, return the self incremented key value.
*/
PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//set up? Value of
pst.setObject(1, "Testing department");
pst.setObject(2, "Test item data");
//Execute sql
int len = pst.executeUpdate();//Returns the number of records affected
if(len>0){
//Get the key value returned by the server from pst
ResultSet rs = pst.getGeneratedKeys();
//Because there may be multiple key values here, and because the insert statement can add multiple lines at the same time, it is encapsulated with ResultSet
//Since only one item is added here, it is judged by if
if(rs.next()){
Object key = rs.getObject(1);
System.out.println("Self increasing key value did =" + key);
}
}
//4. Shut down
pst.close();
conn.close();
}
}
2.3 batch processing
/*
* Batch processing:
* Batch processing sql
*
* For example:
* (1)Adding multiple records in order details
* (2)Batch add simulation data
* ...
*
* What's the difference between not using batch processing and using batch processing?
* Batch processing is much more efficient
*
* How to perform batch operation?
* (1)Add a parameter to the url
* rewriteBatchedStatements=true
* Then our url becomes jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
* there?, express? The following is the parameters passed from the client to the server. Multiple parameters are directly used & split
* (2)Different calling methods
* pst.addBatch();
* int[] all = pst.executeBatch();
*
* Note: if you want to add in batches, insert values instead of value
*/
public class TestBatch {
public static void main(String[] args) throws Exception{
long start = System.currentTimeMillis();
//For example: in department table t_ Add 1000 pieces of simulation data to the Department
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true", "root", "123456");
//3. Execute sql
String sql = "insert into t_department values(null,?,?)";
PreparedStatement pst = conn.prepareStatement(sql);
//set up? Value of
for (int i = 1; i <=1000; i++) {
pst.setObject(1, "Simulation Department"+i);
pst.setObject(2, "Introduction to simulation Department"+i);
pst.addBatch();//Add to a group of batch operations and save a piece for processing
/* if(i % 500 == 0){//Sometimes I save some and execute some
//2.implement
pst.executeBatch();
//3.empty
pst.clearBatch();
}*/
}
pst.executeBatch();
//4. Shut down
pst.close();
conn.close();
long end = System.currentTimeMillis();
System.out.println("Time consuming:" + (end - start));//Time: 821
}
}
2.4 transactions
/*
* mysql By default, each connection is automatically committed.
* Then, in the JDBC section, if there are multiple statements that want to form a transaction to be executed together, how to set the manual transaction submission in JDBC?
* (1)Set up manual commit transactions before execution
* Connection Object of setAutoCommit(false)
* (2)success:
* Connection Object of commit();
* Failed:
* Connection Object of rollback();
*
* Supplementary notes:
* In order to form the habit we should, set the Connection object back to automatic submission before closing the Connection object
* (3)Connection Object of setAutoCommit(true)
*
* Because our current connection is to establish a new connection, if it is not restored to automatic submission, it will not be affected.
* However, in our later actual development, each connection obtained is not necessarily a new connection, but an old connection obtained from the connection pool, and you don't really close it when you close it,
* Instead, it returns the connection pool for others to use. In case someone else gets it and thinks it is submitted automatically without commit, the final data will not succeed.
*/
public class TestTransaction {
public static void main(String[] args) throws Exception{
/*
* Generally, if transaction processing is involved, the business logic will be more complex.
* For example: when shopping cart settlement:
* (1)Add a record to the order form
* (2)Add multiple records of order details in the order details table (indicating what the order bought)
* (3)Modify the sales volume and inventory of the commodity table
* ...
* So today, we are here to focus on the operation of transactions and not lead to our understanding due to the influence of complex business logic
* Two modification statements are used to simulate a simple transaction.
* update t_department set description = 'xx' where did = 2;
* update t_department set description = 'yy' where did = 3;
*
* I hope these two-day sentences will either succeed together or roll back together
* In order to create failure, I deliberately wrote the second sentence wrong
* update t_department set description = 'yy' (Missing where) did = 3;
*/
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
//Set up manual commit transactions
conn.setAutoCommit(false);
//3. Execute sql
String sql1 = "update t_department set description = 'xx' where did = 2";
String sql2 = "update t_department set description = 'yy' did = 3";//This is wrong
//sql using prepareStatement can also be used without?
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql1);
int len = pst.executeUpdate();
System.out.println("Article 1:" + (len>0?"success":"fail"));
pst = conn.prepareStatement(sql2);
len = pst.executeUpdate();
System.out.println("Article 2:" + (len>0?"success":"fail"));
//If it's all successful, commit the transaction
System.out.println("Submit");
conn.commit();
} catch (Exception e) {
System.out.println("RollBACK ");
//Failed to rollback
conn.rollback();
}
//4. Shut down
pst.close();
conn.setAutoCommit(true);//Revert to auto commit
conn.close();
}
}
Chapter 3 database connection pool
1. What is database pooling
The buffer of the connection object. Responsible for application, allocation management and release of connections.
2. Why use database connection pool
The database connection pool is not used, and new connections are obtained through DriverManager every time. When they are used up, they are directly discarded and disconnected. The utilization rate of connections is too low and wasteful.
The pressure is too great for the database server. Our database server and Java program cannot control the number of connections, which can easily lead to the collapse of the database server.
We want to manage connections.
We can establish a connection pool, which can hold a certain number of connection objects. At the beginning, we can create some connection objects for users first,
When users want to take the connection object, they can take it directly from the pool without creating a new one, which can also save time. Then, after the user runs out, put it back and others can use it again.
It can improve the utilization of connections. When the existing connections in the pool are used up, the connection pool can apply to the server for new connections to be placed in the pool.
Until the connection in the pool reaches the "maximum number of connections", you can't apply for a new connection. If you don't get the connection, you can only wait.
3. There are many ready-made database connection pool technologies on the market:
- The database connection pool of JDBC uses javax sql. DataSource means that DataSource is just an interface (commonly referred to as data source), which is usually implemented by servers (Weblogic, WebSphere, Tomcat) and also provided by some open source organizations:
- DBCP is a database connection pool provided by Apache, which is relatively c3p0 fast. However, due to its own BUG, hibernate 3 no longer provides support
- C3P0 is a database connection pool provided by an open source organization, which is relatively slow and stable
- Proxool is an open source project database connection pool under sourceforge. It has the function of monitoring the status of the connection pool, and its stability is c3p0 poor
- BoneCP is a database connection pool provided by an open source organization with high speed
- Druid is a database connection pool provided by Alibaba. It is said to be a database connection pool integrating the advantages of DBCP, C3P0 and Proxool
4. Ali's Druid connection pool technology
(1) Add jar package
For example: druid-1.1.10 jar
(2) Code steps
Step 1: establish a database connection pool
Step 2: set connection pool parameters
Step 3: get the connection
public class TestPool {
public static void main(String[] args) throws SQLException {
//1. Create a data source (database connection pool) object
DruidDataSource ds =new DruidDataSource();
//2. Set parameters
//(1) Set basic parameters
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/test");
ds.setUsername("root");
ds.setPassword("123456");
//(2) Set the number of connections and other parameters
ds.setInitialSize(5);//At the beginning, apply for 5 connections in advance. It's not enough. Rewrite the application
ds.setMaxActive(10);//No more than 10 at most. If all 10 are used up and haven't been returned, there will be waiting
ds.setMaxWait(1000);//The user can wait up to 1000 milliseconds. If no one returns the 1000 milliseconds, it will be abnormal
//3. Get connection
for (int i = 1; i <=15; i++) {
Connection conn = ds.getConnection();
System.out.println("The first"+i+"Number:" + conn);
//If it's not closed here, it's not closed yet
// conn.close();# It is closed here and returned to the pool
}
}
}
| to configure | default | explain |
|---|---|---|
| name | The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If there is no configuration, a name will be generated in the format of "DataSource -" + system identityHashCode(this) | |
| jdbcUrl | The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
| username | User name to connect to the database | |
| password | Password to connect to the database. If you don't want the password written directly in the configuration file, you can use ConfigFilter. See here for details: https://github.com/alibaba/druid/wiki/ Using ConfigFilter | |
| driverClassName | Automatic identification according to url is optional. If druid is not configured, dbType will be automatically identified according to url, and then corresponding driverclassname will be selected (under recommended configuration) | |
| initialSize | 0 | The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection |
| maxActive | 8 | Maximum number of connection pools |
| maxIdle | 8 | It is no longer used, and the configuration has no effect |
| minIdle | Minimum number of connection pools | |
| maxWait | The maximum waiting time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use the unfair lock by configuring the useUnfairLock attribute to true. | |
| poolPreparedStatements | false | Whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql. |
| maxOpenPreparedStatements | -1 | To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements will be automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100 |
| validationQuery | The sql used to check whether the connection is valid requires a query statement. If validationQuery is null, testonmirror, testOnReturn and testwhiteidle will not work. | |
| testOnBorrow | true | When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance. |
| testOnReturn | false | When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance |
| testWhileIdle | false | It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for connection. If the idle time is greater than timebetween evictionrunsmillis, execute validationQuery to check whether the connection is valid. |
| timeBetweenEvictionRunsMillis | It has two meanings: 1) the destroy thread will detect the connection interval; 2) the judgment basis of testwhiteidle. See the description of testwhiteidle attribute for details | |
| numTestsPerEvictionRun | No longer used, a DruidDataSource only supports one EvictionRun | |
| minEvictableIdleTimeMillis | ||
| connectionInitSqls | sql executed during physical connection initialization | |
| exceptionSorter | According to dbType, the connection will be discarded when the database throws some unrecoverable exceptions | |
| filters | The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall | |
| proxyFilters | The type is List. If filters and proxyFilters are configured at the same time, it is a combination relationship, not a replacement relationship |
Chapter 4 encapsulating JDBC tools
Configuration file: Src / JDBC properties
#key=value driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test username=root password=123456 initialSize=5 maxActive=10 maxWait=1000
JDBC tools tool class:
package com.atguigu.util;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
/*
* Gets or releases the tool class of the connection
*/
public class JDBCTools {
// 1. Data source, i.e. connection pool
private static DataSource dataSource;
// 2. ThreadLocal object
private static ThreadLocal<Connection> threadLocal;
static {
try {
//1. Read druip Properties file
Properties pro = new Properties();
pro.load(JDBCTools.class.getClassLoader().getResourceAsStream("druid.properties"));
//2. Connection pool
dataSource = DruidDataSourceFactory.createDataSource(pro);
//3. Create thread pool
threadLocal = new ThreadLocal<>();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Method of obtaining connection
*
* @return
* @throws SQLException
*/
public static Connection getConnection() {
// Get connection from current thread
Connection connection = threadLocal.get();
if (connection == null) {
// Get a connection from the connection pool
try {
connection = dataSource.getConnection();
// Bind connection to current thread
threadLocal.set(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
return connection;
}
/**
* Method of releasing connection
*
* @param connection
*/
public static void releaseConnection() {
// Gets the connection in the current thread
Connection connection = threadLocal.get();
if (connection != null) {
try {
connection.close();
// Removes closed connections from the current thread
threadLocal.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ThreadLocal is introduced as follows:
Java. Java is available in JDK version 1.2 Lang. ThreadLocal, which provides a new idea to solve the concurrency problem of multithreaded programs. Using this tool class, you can write beautiful multithreaded programs very concisely. It is usually used to manage shared database connections, sessions, etc. in multithreading
ThreadLocal is used to save a thread shared variable. The reason is that in Java, there is a threadlocalmap < ThreadLocal, Object > in each thread object. Its key is a ThreadLocal, and object is the shared variable of the thread. The map is operated through the set and get methods of ThreadLocal. For the same static ThreadLocal, different threads can only get, set and remove their own variables from it without affecting the variables of other threads.
1,ThreadLocal.get: get the value of the current thread shared variable in ThreadLocal.
2,ThreadLocal.set: sets the value of the current thread shared variable in ThreadLocal.
3,ThreadLocal.remove: removes the value of the current thread shared variable in ThreadLocal.
Chapter V packaging BasicDAOImpl
package com.atguigu.test08.dao;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import com.atguigu.test06.threadlocal.JDBCTools2;
/*
* Provide general methods for adding, deleting, modifying and querying all tables
*/
public class BasicDAO1 {
/*
* General methods of addition, deletion and modification
*/
protected int update(String sql,Object... args) throws SQLException{
//1. Get connection object
Connection conn = JDBCTools2.getConnection();
//2. Write sql and replace it with formal parameters
//3. Gets the Statement or PreparedStatement object
PreparedStatement pst = conn.prepareStatement(sql);
//set up? Value of
//Because I don't know if there is in sql?, And? Set the why value, which is passed in through formal parameters
//Object... args is a deformable parameter. 0~n values can be passed in
//If not, it means No? Need to set
//If n values are passed in, it indicates that there are n values in sql? Need to set
if(args!=null && args.length>0){
for (int i = 0; i < args.length; i++) {
//The subscript of the array starts from 0, pst? The serial number of starts from 1, so i+1 is used here
pst.setObject(i+1, args[i]);
}
}
//4. Execute sql
int len = pst.executeUpdate();
//5. Shut down
pst.close();
JDBCTools2.free();
return len;
}
/*
* Method for general query of multiple java bean objects
*/
protected <T> ArrayList<T> getList(Class<T> clazz,String sql,Object... args) throws Exception{
//1. Get connection
Connection conn = JDBCTools2.getConnection();
//2. Write sql and pass it in by formal parameters
//3. Gets the PreparedStatement object
PreparedStatement pst = conn.prepareStatement(sql);
//4. Setting?, Passed in by formal parameter
if(args!=null && args.length>0){
for (int i = 0; i < args.length; i++) {
//The subscript of the array starts from 0, pst? The serial number of starts from 1, so i+1 is used here
pst.setObject(i+1, args[i]);
}
}
//5. Execute sql
ResultSet rs = pst.executeQuery();
/*
* How to turn the data in the ResultSet result set into a java bean object, put it into the ArrayList object, and return it
*/
ArrayList<T> list = new ArrayList<>();
/*
* To get the total number of rows from the ResultSet result set, decide how many objects to create
* To get a total of several columns from the ResultSet result set, decide how many attributes to assign values to
* ResultSet In the result set object, there is a method ResultSetMetaData getMetaData() to obtain the metadata of the result set
* Metadata is the data that describes the data in the result set, such as column number, column name, etc
*/
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();//Get the number of columns
while(rs.next()){//Create a java bean object once
//(1) Create a java bean object
T t = clazz.newInstance();//This method requires that the java bean class be constructed with or without parameters
//(2) Set the property value of the object
/*
* To reflect operation attributes:
* ①Get the Class object. Now you have it
* ②Get property object Field
* Field f = clazz.getDeclaredField("Attribute name "");
* ③Create java bean object, already created
* ④Set the accessibility of the property setAccessible(true)
* ⑤Set the value of the property
*/
for (int i = 0; i < count; i++) {//A total of count attributes need to be assigned values
// Field f = clazz. Getdeclaraedfield ("property name");
String fieldName = metaData.getColumnLabel(i+1);//Gets the field name of the column
Field f = clazz.getDeclaredField(fieldName);
f.setAccessible(true);
f.set(t, rs.getObject(i+1));//rs.getObject(i+1) gets the value of the column
}
//(3) Put the Java Bean object into the list
list.add(t);
}
//6. Shut down
rs.close();
pst.close();
JDBCTools2.free();
return list;
}
protected <T> T getBean(Class<T> clazz,String sql,Object... args) throws Exception{
return getList(clazz,sql,args).get(0);
}
}
Use BasicDAOImpl to add, delete, modify and query employees
[the external chain picture transfer fails. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-hkq9oqxu-16458863693) (IMGs / 1557457111280. PNG)]
Example code: employeedao java
public interface EmployeeDAO {
void addEmployee(Employee emp);
void updateEmployee(Employee emp);
void deleteByEid(int eid);
Employee getByEid(int eid);
ArrayList<Employee> getAll();
}
Example code: employeedaoimpl java
package com.atguigu.test08.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import com.atguigu.bean.Employee;
public class EmployeeDAOImpl extends BasicDAO1 implements EmployeeDAO {
@Override
public void addEmployee(Employee emp) {
//`ename`,`tel`,`gender`,`salary`,`commission_pct`,`birthday`,
//`hiredate`,`job_id`,`email`,`mid`,`address`,`native_place`,`did`
String sql = "insert into t_employee values(null,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
update(sql, emp.getEname(),emp.getTel(),emp.getGender(),emp.getSalary(),emp.getCommissionPct(),
emp.getBirthday(),emp.getHiredate(),emp.getJobId(),emp.getEmail(),emp.getMid(),emp.getAddress(),
emp.getNativePlace(),emp.getDid());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void updateEmployee(Employee emp) {
String sql = "update t_employee set `ename`=?,`tel`=?,`gender`=?,`salary`=?,`commission_pct`=?,`birthday`=?,`hiredate`=?,`job_id`=?,`email`=?,`mid`=?,`address`=?,`native_place`=?,`did`=? where eid=?";
try {
update(sql, emp.getEname(),emp.getTel(),emp.getGender(),emp.getSalary(),emp.getCommissionPct(),
emp.getBirthday(),emp.getHiredate(),emp.getJobId(),emp.getEmail(),emp.getMid(),emp.getAddress(),
emp.getNativePlace(),emp.getDid(),emp.getEid());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void deleteByEid(int eid) {
String sql = "delete from t_employee where eid=?";
try {
update(sql, eid);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public Employee getByEid(int eid) {
//Here, the attribute name of the corresponding java bean is specified by alias in sql
String sql = "select `eid`,`ename`,`tel`,`gender`,`salary`,`commission_pct` commissionPct ,`birthday`,`hiredate`,`job_id` jobId,`email`,`mid`,`address`,`native_place` nativePlace,`did` from t_employee where eid=?";
Employee emp = null;
try {
emp = getBean(Employee.class, sql, eid);
} catch (Exception e) {
throw new RuntimeException(e);
}
return emp;
}
@Override
public ArrayList<Employee> getAll() {
//Here, the attribute name of the corresponding java bean is specified by alias in sql
String sql = "select `eid`,`ename`,`tel`,`gender`,`salary`,`commission_pct` commissionPct ,`birthday`,`hiredate`,`job_id` jobId,`email`,`mid`,`address`,`native_place` nativePlace,`did` from t_employee";
ArrayList<Employee> list = new ArrayList<Employee>();
try {
list = getList(Employee.class, sql);
} catch (Exception e) {
throw new RuntimeException(e);
}
return list;
}
}
Chapter 6 Apache DBUtils
Commons dbutils is an open source JDBC tool class library provided by Apache organization. It is a simple encapsulation of JDBC and has very low learning cost. Using dbutils can greatly simplify the workload of JDBC coding without affecting the performance of the program.
QueryRunner class encapsulates the execution of SQL and is thread safe.
(1) You can add, delete, modify, query, batch process
(2) It is considered that transaction processing needs to share Connection.
(3) The most important thing of this class is to simplify SQL query. It can be combined with ResultSetHandler to complete most database operations and greatly reduce the amount of coding.
(1) Renew
public int update(Connection conn, String sql, Object... params) throws SQLException: used to perform an update (insert, update or delete) operation.
...
(2) Insert
public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException: only INSERT statements are supported, where RSH - the handler used to create the result object from the resultset of auto generated keys Return value: An object generated by the handler That is, the automatically generated key value
...
(3) Batch processing
public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException: INSERT, UPDATE, or DELETE statements
public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException: only INSERT statements are supported
...
(4) Use the QueryRunner class to implement the query
public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException: executes a query operation. In this query, each element value in the object array is used as the replacement parameter of the query statement. This method handles the creation and closing of PreparedStatement and ResultSet by itself.
...
The ResultSetHandler interface is used to handle Java sql. Resultset to convert the data into another form as required. The ResultSetHandler interface provides a separate method: Object handle (java.sql.ResultSet rs). The return value of this method will be used as the return value of the query() method of the QueryRunner class.
The interface has the following implementation classes that can be used:
- BeanHandler: encapsulate the first row of data in the result set into a corresponding JavaBean instance.
- BeanListHandler: encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
- ScalarHandler: query a single value object
- MapHandler: encapsulate the first row of data in the result set into a Map. key is the column name and value is the corresponding value.
- MapListHandler: encapsulate each row of data in the result set into a Map, and then store it in the List
- ColumnListHandler: store the data of a column in the result set into the List.
- KeyedHandler(name): encapsulate each row of data in the result set into a map, and then save these maps into a map. The key is the specified key.
- ArrayHandler: converts the first row of data in the result set into an object array.
- ArrayListHandler: convert each row of data in the result set into an array and store it in the List.
Example code: basicdaoimpl java
package com.atguigu.test09.dbutil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.atguigu.test06.threadlocal.JDBCTools2;
public class BasicDAO2 {
private QueryRunner qr = new QueryRunner();
protected int update(String sql,Object... args) throws SQLException{
Connection conn = JDBCTools2.getConnection();
int len = qr.update(conn, sql, args);
//QueryRunner can help you close the connection
return len;
}
protected <T> List<T> getList(Class<T> clazz,String sql,Object... args) throws Exception{
Connection conn = JDBCTools2.getConnection();
/*
* ResultSetHandler Interface for processing java sql. Resultset to convert the data into another form as required.
* (1)BeanListHandler Other forms
*/
List<T> list = qr.query(conn, sql, new BeanListHandler<>(clazz), args);
return list;
}
protected <T> T getBean(Class<T> clazz,String sql,Object... args) throws Exception{
Connection conn = JDBCTools2.getConnection();
/*
* ResultSetHandler Interface for processing java sql. Resultset to convert the data into another form as required.
* (2)BeanHandler Other forms
*/
T t = qr.query(conn, sql, new BeanHandler<>(clazz), args);
return t;
}
/*
* General method of querying single value
* For example: total number of employees, maximum wage, average wage, etc
*/
protected Object getObject(String sql,Object... args) throws Exception{
Connection conn = JDBCTools2.getConnection();
/*
* ResultSetHandler Interface for processing java sql. Resultset to convert the data into another form as required.
* (3)ScalarHandler: Query single value object and other forms
*/
Object obj = qr.query(conn, sql, new ScalarHandler<>(), args);
return obj;
}
/*
* General method for querying multiple rows and columns
* For example: average salary of each department
*/
protected List<Map<String, Object>> getMapList(String sql,Object... args) throws Exception{
Connection conn = JDBCTools2.getConnection();
/*
* ResultSetHandler Interface for processing java sql. Resultset to convert the data into another form as required.
* (4)MapListHandler: Encapsulate each row of data in the result set into a Map, and then store it in the List
*/
List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler(), args);
return list;
}
}
Example code:
public interface EmployeeDAO2 {
void addEmployee(Employee emp);
void updateEmployee(Employee emp);
void deleteByEid(int eid);
Employee getByEid(int eid);
List<Employee> getAll();
long empCount();//Query the total number of employees
double avgSalary();//Query the average salary of the whole company
//key is the department number and Double is the average salary
Map<Integer,Double> avgSalaryPerDepartment();
}
Example code:
package com.atguigu.test09.dbutil;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.atguigu.bean.Employee;
public class EmployeeDAOImpl2 extends BasicDAO2 implements EmployeeDAO2 {
@Override
public void addEmployee(Employee emp) {
//`ename`,`tel`,`gender`,`salary`,`commission_pct`,`birthday`,
//`hiredate`,`job_id`,`email`,`mid`,`address`,`native_place`,`did`
String sql = "insert into t_employee values(null,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
update(sql, emp.getEname(),emp.getTel(),emp.getGender(),emp.getSalary(),emp.getCommissionPct(),
emp.getBirthday(),emp.getHiredate(),emp.getJobId(),emp.getEmail(),emp.getMid(),emp.getAddress(),
emp.getNativePlace(),emp.getDid());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void updateEmployee(Employee emp) {
String sql = "update t_employee set `ename`=?,`tel`=?,`gender`=?,`salary`=?,`commission_pct`=?,`birthday`=?,`hiredate`=?,`job_id`=?,`email`=?,`mid`=?,`address`=?,`native_place`=?,`did`=? where eid=?";
try {
update(sql, emp.getEname(),emp.getTel(),emp.getGender(),emp.getSalary(),emp.getCommissionPct(),
emp.getBirthday(),emp.getHiredate(),emp.getJobId(),emp.getEmail(),emp.getMid(),emp.getAddress(),
emp.getNativePlace(),emp.getDid(),emp.getEid());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void deleteByEid(int eid) {
String sql = "delete from t_employee where eid=?";
try {
update(sql, eid);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public Employee getByEid(int eid) {
//Here, the attribute name of the corresponding java bean is specified by alias in sql
String sql = "select `eid`,`ename`,`tel`,`gender`,`salary`,`commission_pct` commissionPct ,`birthday`,`hiredate`,`job_id` jobId,`email`,`mid`,`address`,`native_place` nativePlace,`did` from t_employee where eid=?";
Employee emp = null;
try {
emp = getBean(Employee.class, sql, eid);
} catch (Exception e) {
throw new RuntimeException(e);
}
return emp;
}
@Override
public List<Employee> getAll() {
//Here, the attribute name of the corresponding java bean is specified by alias in sql
String sql = "select `eid`,`ename`,`tel`,`gender`,`salary`,`commission_pct` commissionPct ,`birthday`,`hiredate`,`job_id` jobId,`email`,`mid`,`address`,`native_place` nativePlace,`did` from t_employee";
List<Employee> list = new ArrayList<Employee>();
try {
list = getList(Employee.class, sql);
} catch (Exception e) {
throw new RuntimeException(e);
}
return list;
}
@Override
public long empCount() {
String sql = "select count(1) from t_employee";
Long count = 0L;
try {
Object obj = getObject(sql);
count = (Long) obj;
} catch (Exception e) {
throw new RuntimeException(e);
}
return count;
}
@Override
public double avgSalary() {
String sql = "select avg(salary) from t_employee";
Double avg = 0.0;
try {
avg = (Double) getObject(sql);
} catch (Exception e) {
throw new RuntimeException(e);
}
return avg;
}
@Override
public Map<Integer, Double> avgSalaryPerDepartment() {
String sql = "select did,avg(salary) from t_employee group by did";
Map<Integer, Double> map = new HashMap<>();
try {
List<Map<String, Object>> mapList = getMapList(sql);
/*
* String: The name of the field, for example: did,avg(salary)
* Object: The value of the field, for example 119819.408666666
* {did=1, avg(salary)=19819.408666666666}
{did=2, avg(salary)=11708.5}
{did=3, avg(salary)=70223.0}
{did=4, avg(salary)=12332.0}
{did=5, avg(salary)=11065.5}
*/
for (Map<String, Object> map2 : mapList) {
map.put((Integer)map2.get("did"),(Double)map2.get("avg(salary)"));
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return map;
}
}
Example code:
package com.atguigu.test09.dbutil;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import com.atguigu.bean.Employee;
public class TestEmployeeDAOImp2 {
@Test
public void test1(){
EmployeeDAOImpl2 ed2 = new EmployeeDAOImpl2();
List<Employee> all = ed2.getAll();
for (Employee employee : all) {
System.out.println(employee);
}
}
@Test
public void test2(){
EmployeeDAOImpl2 ed2 = new EmployeeDAOImpl2();
long count = ed2.empCount();
System.out.println(count);
}
@Test
public void test3(){
EmployeeDAOImpl2 ed2 = new EmployeeDAOImpl2();
double avgSalary = ed2.avgSalary();
System.out.println(avgSalary);
}
@Test
public void test4(){
EmployeeDAOImpl2 ed2 = new EmployeeDAOImpl2();
Map<Integer, Double> map = ed2.avgSalaryPerDepartment();
map.forEach((k,v) -> System.out.println(k+"->"+v));
}
}
be careful:
In idea, junit's test method does not support console input by default. You need to find the corresponding configuration file to modify:
Installation directory of idea / bin / idea64 exe. vmoptions
Instead of opening this file, add a configuration on the last line: - deditable java. test. console=true