preface
This is the self-study notes of crazy God's mysql, which is only for reference, learning and continuous updating
The following is a video learning link:
https://www.bilibili.com/video/BV1NJ411J79W
10. JDBC (key)
10.1. Database driver
Driver: sound card, graphics card, database
Our program will deal with the database through database driver!
10.2,JDBC
In order to simplify the (unified database) operation of developers, SUN company provides a (Java database operation) specification, commonly known as JDBC
The implementation of these specifications is done by specific manufacturers
For developers, they only need to master the operation of JDBC interface!
Required packages:
java.sql
javax.sql
You also need to import a database driver package mysql-connector-java-5.1.47.jar
10.3. The first JDBC program
Create test database
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE users( `id` INT PRIMARY KEY, `name` VARCHAR(40), `password` VARCHAR(40), `email` VARCHAR(40), `birthday` DATE ); INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`) VALUES(1, 'zhangsan', '123456', 'zs@sina.com', '1980-12-04'), (2, 'lisi', '123456', 'lisi@sina.com', '1981-12-04'), (3, 'wangwu', '123456', 'wangwu@sina.com', '1979-12-04');
1. Create a project
2. Import database driver
3. Write test code
package com.niu.lession01; import java.sql.*; // My first JDBC program public class jdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1. Load driver Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven // 2. User information and url // useUnicode=true&characterEncoding=utf8&useSSL=true // Delete the secure connection parameter behind the url (with warning) or change it to false // [if the SQL version is greater than the connect version, set it to false] String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username = "root"; String password = "root"; // 3. The Connection is successful. The database object Connection represents the database Connection connection = DriverManager.getConnection(url, username, password); // 4. Object executing SQL Statement statement = connection.createStatement(); // 5. The object executing SQL executes SQL. There may be results. View the returned results String sql = "SELECT * FROM `users`"; ResultSet resultSet = statement.executeQuery(sql); // The returned result value. The result set encapsulates all our query results while (resultSet.next()) { System.out.println("id=" + resultSet.getObject("id")); System.out.println("name=" + resultSet.getObject("name")); System.out.println("password=" + resultSet.getObject("password")); System.out.println("birthday=" + resultSet.getObject("birthday")); System.out.println("====================="); } // 6. Release connection resultSet.close(); statement.close(); connection.close(); } }
Step summary:
1. Load driver
2. Connect to database DriverManger
3. Get the Statement object executing sql
4. Get the returned result set
5. Release connection
DriverManger
// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven Connection connection = DriverManager.getConnection(url, username, password); // connection represents the database // Database settings auto commit // Transaction commit // Transaction rollback connection.rollback(); connection.commit(); connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; // mysql -- 3306 // Protocol: / / host address: port number / database name? Parameter1 & parameter2 & parameter3 // oracle -- 1521 // jdbc:oracle:thin:@localhost:1521:sid
Statement object executing SQL PrepareStatement object executing SQL
String sql = "SELECT * FROM `users`"; statement.executeQuery(); // The query operation returns a ResultSet statement.execute(); // Execute any SQL statement.executeUpdate(); // Update, insert, delete. This is used to return the number of affected rows
ResultSet query result set: encapsulates all query results
Gets the specified data type
resultSet.getObject(); // Use without knowing the column type // If you know the type of column, use the type of column resultSet.getShort(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDate(); resultSet.getObject(); ...
Traversal, pointer
resultSet.beforeFirst(); // Move to the front resultSet.afterLast(); // Move to the back resultSet.next(); // Move to next data resultSet.previous(); // Move to previous line resultSet.absolute(row); // Move to specified row
Release resources
// 6. Release connection resultSet.close(); statement.close(); connection.close(); // Turn off when you run out of resources
10.4 statement object
The statement object in JDBC is used to send SQL statements to the database. To complete the addition, deletion, modification and query of the database, you only need to send the addition, deletion, modification and query statements to the database through this object.
The executeUpdate method of the Statement object is used to send the sql Statement of addition, deletion and modification to the database. After the executeUpdate is executed, an integer will be returned (that is, the addition, deletion and modification Statement causes several rows of data in the database to change).
The Statement.executeQuery method is used to send query statements to the database, and the executeQuery method returns the ResultSet object representing the query results.
CRUD operation - create
Use the executeUpdate(String sql) method to complete the data addition operation. The exemplary operations are as follows:
Statement statement = connection.createStatement(); String sql = "insert into user(...) values(...)"; int num = statement.executeUpdate(sql); if(num > 0) { System.out.println("Insert succeeded!!"); }
CRUD operation - delete
Use the executeUpdate(String sql) method to delete data. The following are examples:
Statement statement = connection.createStatement(); String sql = "delete from user where id = 1"; int num = statement.executeUpdate(sql); if(num > 0) { System.out.println("Delete succeeded!!"); }
CRUD operation - update
Use the executeUpdate(String sql) method to complete the data modification operation. The demonstration operations are as follows:
Statement statement = connection.createStatement(); String sql = "update user set name = '' where name = ''"; int num = statement.executeUpdate(sql); if(num > 0) { System.out.println("Modified successfully!!"); }
CRUD operation - read
Use the executeUpdate(String sql) method to complete the data query operation. The demonstration operations are as follows:
Statement statement = connection.createStatement(); String sql = "select * from user where id=1"; ResultSet rs = statement.executeQuery(sql); while(rs.next()) { // According to the data type of the obtained column, the corresponding methods of rs are called to map to the java object }
code implementation
1. Extraction tool class
(1) . write db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=root
(2) . implement tool class JdbcUtils
package com.niu.lesson02.utils; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); // 1. The driver is loaded only once Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } // Get connection public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } // Release connection public static void release(Connection conn, Statement st, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (st != null) { try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
2. Write the method of addition, deletion and modification, executeUpdate
(1) . add TestInsert
package com.niu.lesson02; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection st = conn.createStatement(); // Get the execution object of SQL String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`) \n" + "VALUES(4, 'jiahui', '123456', 'jiahui@163.com', '1998-12-01')"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("Insert succeeded!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
(2) Delete, TestDelete
package com.niu.lesson02; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestDelete { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection st = conn.createStatement(); // Get the execution object of SQL String sql = "DELETE FROM `users` WHERE id = '4'"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("Delete succeeded!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
(3) , change TestUpdate
package com.niu.lesson02; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection st = conn.createStatement(); // Get the execution object of SQL String sql = "UPDATE `users` SET `name` = 'lixiaofeng', `email` = '127836@163.com' WHERE id = 3"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("Update succeeded!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
3. Query executeQuery
package com.niu.lesson02; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection st = conn.createStatement(); // Get the execution object of SQL String sql = "select * from users"; rs = st.executeQuery(sql); // A result set will be returned after the query while (rs.next()) { System.out.println("name = " + rs.getObject("name")); } } catch (SQLException throwables) { throwables.printStackTrace(); } } }
Project specific structure diagram
SQL injection problem
SQL has a vulnerability. It will be attacked, resulting in data leakage and SQL will be spliced
package com.niu.lesson02; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQL injection { public static void main(String[] args) { login("' or '1=1", "'or '1=1"); // Skill input } public static void login(String username, String password) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection st = conn.createStatement(); // Get the execution object of SQL String sql = "select * from users where name = '" + username + "' and password = '" + password + "'"; rs = st.executeQuery(sql); // A result set will be returned after the query while (rs.next()) { System.out.println("Login successful!!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
10.5 PreparedStatement object
PreparedStatement prevents SQL injection. More efficient!
1. Add
package com.niu.lesson03; import com.niu.lesson02.utils.JdbcUtils; import java.util.Date; import java.sql.*; public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get connection // difference // use? Placeholder instead of parameter String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" + "VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); // Precompiled sql, write sql first, and then do not execute // Assign parameters manually st.setInt(1, 5); st.setString(2, "niu"); st.setString(3, "123456"); st.setString(4, "34570@163.com"); // Note: sql.Date database (Java. sql.Date) // Util.date Java new date(). Gettime() gets the timestamp st.setDate(5, new java.sql.Date(new Date().getTime())); // implement int i = st.executeUpdate(); if (i > 0) { System.out.println("Insert succeeded!!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
2. Delete
package com.niu.lesson03; import com.niu.lesson02.utils.JdbcUtils; import java.sql.*; public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection String sql = "delete from users where id = ?"; st = conn.prepareStatement(sql); // Precompile sql and then do not execute st.setInt(1, 4); int i = st.executeUpdate(); // Execute sql if (i > 0) { System.out.println("Delete succeeded!!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
3. Renew
package com.niu.lesson03; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "update users set `name` = ? where id = ?;"; st = conn.prepareStatement(sql); st.setString(1, "Luo Ji"); st.setInt(2, 3); int i = st.executeUpdate(); if (i > 0) { System.out.println("Modified successfully!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
4. Inquiry
package com.niu.lesson03; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters // Suppose there are escape characters, such as' will be directly escaped String sql = "select * from users where id = ?"; // Write SQL st = conn.prepareStatement(sql); // precompile st.setInt(1, 3); // Transfer parameters rs = st.executeQuery(); // implement while (rs.next()) { System.out.println("id:" + rs.getObject("id")); System.out.println("name:" + rs.getObject("name")); System.out.println("password:" + rs.getObject("password")); System.out.println("==============="); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
5. Prevent SQL injection
package com.niu.lesson03; import com.niu.lesson02.utils.JdbcUtils; import java.sql.*; public class SQL injection { public static void main(String[] args) { // login("'' or '1=1'", "123456"); // Skill input login("Luo Ji", "123456"); // Skill input } public static void login(String username, String password) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Get database connection // PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters // Suppose there are escape characters, such as' will be directly escaped String sql = "select * from users where `name` = ? and `password` = ?"; // Mybatis st = conn.prepareStatement(sql); // Get the execution object of SQL st.setString(1, username); st.setString(2, password); rs = st.executeQuery(); // A result set will be returned after the query while (rs.next()) { System.out.println("Login succeeded!!"); System.out.println(rs.getObject("name")); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
10.7. Use IDEA to connect to the database
Note here that you must import the previous package: mysql-connector-java-5.1.47.jar
After the connection is successful, you can select a database
Double click the database to view the table information
Update data
Write SQL
Connection failed, find the reason
10.8 affairs
Either all succeed or all fail
ACID principle
Atomicity: either complete or not complete
Consistency: the total number remains unchanged
Isolation: multiple processes do not interfere with each other
Persistence: once the commit is irreversible, it is persisted to the database
Isolation issues:
Dirty read: a transaction reads another uncommitted transaction
Non repeatable reading: in the same transaction, the data in the table is repeatedly read, and the table data changes
Virtual reading (unreal reading): in a transaction, data inserted by others is read, resulting in inconsistent results before and after reading
code implementation
1. Start transaction conn.setAutoCommit(false)
2. After a group of business is executed, submit the transaction
3. You can define the rollback statement displayed in the catch statement, but the default failure will rollback
/*Create account table*/ create table account( id int primary key auto_increment, name varchar(40), money float ); /*Insert test data*/ insert into account(name, money) values('A', 1000); insert into account(name, money) values('B', 1000); insert into account(name, money) values('C', 1000);
package com.niu.lesson04; import com.niu.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestTransaction1 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // Turn off the automatic submission of the database and start the transaction automatically conn.setAutoCommit(false); // Open transaction String sql1 = "update account set money = money - 100 where `name` = 'A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); // int i = 1 / 0; // report errors String sql2 = "update account set money = money + 100 where `name` = 'B'"; st = conn.prepareStatement(sql2); st.executeUpdate(); // Business completion, commit transaction conn.commit(); System.out.println("success!"); } catch (SQLException throwables) { try { conn.rollback(); // Roll back the transaction if it fails } catch (SQLException e) { e.printStackTrace(); } throwables.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } } }
10.9. Database connection pool
Database connection - execution completed - release
Connection - release very wasteful system resources
Pooling Technology: prepare some pre prepared resources and connect the pre prepared resources
Minimum number of connections: 10
Maximum connections: 15
Waiting timeout: 100ms
Write a connection pool to implement an interface DataSourse
Implementation of open source data source (ready to use)
DBCP
C3P0
Druid: Alibaba
After using these database connection pools, we don't need to write code to connect to the database in the project development!
DBCP
Required jar package
commons-dbcp-1.4 , commons-pool-1.6
DBCP profile:
# File name: dbcpconfig.properties # The name in the connection settings is defined in the DBCP data source driver.ClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterCoding=utf8&useSSL=false username=root password=root # Initialize connection initialSize=10 # Maximum number of connections maxActive=50 # Maximum idle connections maxIdle=20 # Minimum idle connection minIdle=5 # Timeout wait time in milliseconds 60000 MS / 1000 = 60 ms maxWait=60000 #The format of the connection property attached when the JDBC driver establishes a connection must be: [property name = property;] #Note: the user and password attributes will be explicitly passed, so there is no need to include them here. connectionProperties=useUnicode=true;characterEncoding=UTF8 #Specifies the auto commit status of connections created by the connection pool. defaultAutoCommit=true #driver default specifies the read-only status of connections created by the connection pool. #If this value is not set, the "setReadOnly" method will not be called. (some drivers do not support read-only mode, such as Informix) defaultReadOnly= #driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool. #The available values are one of the following: (see javadoc for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
Tools Utils:
package com.niu.lesson05.utils; import org.apache.commons.dbcp.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtilsDbcp { private static DataSource dataSource = null; static { try{ InputStream in = JdbcUtilsDbcp.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); // Create data source factory pattern -- > create dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } // Get connection public static Connection getConnection() throws SQLException { return dataSource.getConnection(); // Get connection from data source } // Release connection public static void release(Connection conn, Statement st, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (st != null) { try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
Test code:
package com.niu.lesson05; import com.niu.lesson02.utils.JdbcUtils; import com.niu.lesson05.utils.JdbcUtilsDbcp; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class TestDbcp { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtilsDbcp.getConnection(); // Get the connection. Replace it with Dbcp here // difference // use? Placeholder instead of parameter String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" + "VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); // Precompiled sql, write sql first, and then do not execute // Assign parameters manually st.setInt(1, 4); st.setString(2, "niu"); st.setString(3, "123456"); st.setString(4, "34570@163.com"); // Note: sql.Date database (Java. sql.Date) // Util.date Java new date(). Gettime() gets the timestamp st.setDate(5, new java.sql.Date(new Date().getTime())); // implement int i = st.executeUpdate(); if (i > 0) { System.out.println("Insert succeeded!!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtilsDbcp.release(conn, st, rs); // Change here to Dbcp } } }
C3P0
Required jar package
c3p0-0.9.5.5.jar,mchange-commons-java-0.2.19.jar
C3P0 configuration file
<?xml version="1.0" encoding="UTF-8" ?> <c3p0-config> <!--c3p0 Default (default) configuration for If in code ComboPooledDataSource ds=new ComboPooledDataSource();This means that c3p0 Default for (default)--> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false</property> <property name="user">root</property> <property name="password">root</property> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> <!--c3p0 Named configuration for If in code ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");This means that name yes MySQL Configuration of--> <named-config name="MySQL"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false</property> <property name="user">root</property> <property name="password">root</property> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </named-config> </c3p0-config>
Utility class Utils
package com.niu.lesson05.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtilsC3p0 { private static ComboPooledDataSource dataSource = null; static { try{ // Code version configuration // dataSource = new ComboPooledDataSource(); // dataSource.setDriverClass(); // dataSource.setUser(); // dataSource.setPassword(); // dataSource.setJdbcUrl(); // // dataSource.setMaxPoolSize(); // dataSource.setMinPoolSize(); // Create data source factory pattern -- > create dataSource = new ComboPooledDataSource("MySQL"); // Configuration file writing } catch (Exception e) { e.printStackTrace(); } } // Get connection public static Connection getConnection() throws SQLException { return dataSource.getConnection(); // Get connection from data source } // Release connection public static void release(Connection conn, Statement st, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (st != null) { try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
Test code:
package com.niu.lesson05; import com.niu.lesson05.utils.JdbcUtilsC3p0; import com.niu.lesson05.utils.JdbcUtilsDbcp; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class TestC3p0 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtilsC3p0.getConnection(); // Get the connection. Here it is changed to C3P0 // difference // use? Placeholder instead of parameter String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" + "VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); // Precompiled sql, write sql first, and then do not execute // Assign parameters manually st.setInt(1, 7); st.setString(2, "niu"); st.setString(3, "123456"); st.setString(4, "34570@163.com"); // Note: sql.Date database (Java. sql.Date) // Util.date Java new date(). Gettime() gets the timestamp st.setDate(5, new java.sql.Date(new Date().getTime())); // implement int i = st.executeUpdate(); if (i > 0) { System.out.println("Insert succeeded!!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtilsC3p0.release(conn, st, rs); // Change here to C3P0 } } }
summary
No matter what data source is used, the essence is the same. The DataSource interface will not change, and the method will not change