Shulou(Shulou.com)06/03 Report--

This article mainly introduces the Java database connection JDBC example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

1. What is JDBC

JDBC (JavaDataBase Connectivity) is the Java database connection, to put it bluntly, it is to use the Java language to operate the database. It turns out that we operate the database by using SQL statements in the console, and JDBC sends SQL statements to the database in Java language.

2. The principle of JDBC

The early geniuses of SUN wanted to write a set of API that could connect to all the databases in the world, but when they first started, they found that this was an impossible task because the database servers of different vendors were so different. Later, SUN began to discuss with database vendors, and finally came to the conclusion that SUN provides a set of specifications for accessing the database (that is, a set of interfaces) and provides protocol standards for connecting to the database, and then each database manufacturer will follow the specification of SUN to provide a set of API to access their own database servers. The specification provided by SUN is named JDBC, while the API provided by various manufacturers that follow the JDBC specification and can access their own database is called driver! JDBC is the interface, and the JDBC driver is the implementation of the interface, no driver can not complete the database connection! Each database vendor has its own driver to connect to its own company's database.

3. Demonstrate the use of JDBC

By downloading the driver jar file for MySQL, add it to the middle of the project and specify it as the downloaded driver when registering the driver.

Package jdbc;import com.mysql.jdbc.Driver; / / this is our driven path import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class Jdbc01 {public static void main (String [] args) throws SQLException {/ / 1. Registered driver Driver driver = new Driver (); / / 2. Get the connection / / jdbc:mysql:// specified protocol localhost connection address 3306 listening port test_table connection database String url = "jdbc:mysql://localhost:3306/test_table"; Properties properties = new Properties (); / / user and password specified can not be arbitrarily changed properties.setProperty ("user", "root") / / properties.setProperty ("password", "161142"); Connection connect = driver.connect (url, properties); / / equivalent to network connection / / 3. Execute sql statement / / String sql = "insert into actor values (null,'syj',' female', '2000-05-26); String sql =" update actor set name =' xhj' where id = 2 "; Statement statement = connect.createStatement (); int rows = statement.executeUpdate (sql); / / return the number of rows affected if (rows > 0) System.out.println (" added successfully ") Else System.out.println ("add failed"); / / 4. Close connection resource statement.close (); connect.close ();}} 4. Database connection method public class JdbcConn {@ Test / * the first * / public void testConn01 () throws SQLException {/ / get Driver implementation class object Driver driver = new Driver (); String url = "jdbc:mysql://localhost:3306/test_table"; Properties properties = new Properties () Properties.setProperty ("user", "root"); properties.setProperty ("password", "161142"); Connection connect = driver.connect (url, properties); System.out.println (connect) } @ Test / * the second * / public void testConn02 () throws Exception {/ / use reflection to load Driver classes and load dynamically. You can flexibly use various databases Class aClass = Class.forName ("com.mysql.jdbc.Driver"); Driver driver = (Driver) aClass.getDeclaredConstructor (). NewInstance (); String url = "jdbc:mysql://localhost:3306/test_table" through configuration files. Properties properties = new Properties (); properties.setProperty ("user", "root"); properties.setProperty ("password", "161142"); Connection connect = driver.connect (url, properties); System.out.println (connect) } @ Test / * third * / DriverManager unified to manage Driver public void testConn03 () throws Exception {/ / use reflection to load the Driver class Class aClass = Class.forName ("com.mysql.jdbc.Driver"); Driver driver = (Driver) aClass.getDeclaredConstructor (). NewInstance (); / / create url and user and password String url = "jdbc:mysql://localhost:3306/test_table" String user = "root"; String password = "161142"; DriverManager.registerDriver (driver); / / register Driver driver Connection connection = DriverManager.getConnection (url, user, password); System.out.println (connection) } @ Test / * fourth * / public void testConn04 () throws Exception {/ / load the Driver class Class.forName ("com.mysql.jdbc.Driver") using reflection / * Class.forName ("com.mysql.jdbc.Driver") automatically completes the registration driver when the underlying Driver is loaded, simplifies the code / / automatically loads the static code block static {try {DriverManager.registerDriver (new Driver ()) when the underlying Driver is loaded. } catch (SQLException var1) {throw new RuntimeException ("Can't register driver!");} * / Class.forName ("com.mysql.jdbc.Driver"); / * Class.forName ("com.mysql.jdbc.Driver") This sentence can also remove the mysql driver 5.1.6 without CLass.forName ("com.mysql.jdbc.Driver") Since jdbc4 has been used since jdk1.5, it is no longer necessary to display the class name in the META-INF\ services\ java.sqI.Driver text under the driver jar package instead of calling the class.forName () registration driver. It is more clear that * / create url and user and password String url = "jdbc:mysql://localhost:3306/test_table". String user = "root"; String password = "161142"; Connection connection = DriverManager.getConnection (url, user, password); System.out.println (connection) } @ Test / * the fifth (recommended) * / public void testConn05 () throws Exception {/ / in the case of mode 4, put the information in the configuration file to facilitate the subsequent sustainable operation / / obtain the configuration file information Properties properties = new Properties (); properties.load (new FileInputStream ("src\\ mysql.properties")) / / get relevant information String user = properties.getProperty ("user"); String password = properties.getProperty ("password"); String url = properties.getProperty ("url"); String driver = properties.getProperty ("driver"); Class.forName (driver); / / load Driver class. It is recommended to add Connection connection = DriverManager.getConnection (url, user, password); System.out.println (connection). }} 5. Query of JDBC

Use ResultSet to record query results

ResultSet: the underlying ArrayList is used to store each row of data (two-dimensional byte array, where each dimension represents one data in a row)

Resultment: an object used to execute a static SQL statement and return the results it generates, which is an interface that needs to be implemented by various database vendors. (in practice, we don't usually use this.)

Public class jdbc03 {public static void main (String [] args) throws Exception {Properties properties = new Properties (); properties.load (new FileInputStream ("src\\ mysql.properties")); String user = properties.getProperty ("user"); String password = properties.getProperty ("password"); String url = properties.getProperty ("url"); String driver = properties.getProperty ("driver"); Class.forName (driver) Connection connection = DriverManager.getConnection (url, user, password); Statement statement = connection.createStatement (); ResultSet resultSet = statement.executeQuery ("select id, `name`, sex,borndate from actor;"); while (resultSet.next ()) {/ / resultSet.previous (); / / move up one line int id = resultSet.getInt (1); / / int id = resultSet.getInt ("id") / / you can also get String name = resultSet.getString (2); String sex = resultSet.getString (3); Date date = resultSet.getDate (4); / / Object object = resultSet.getObject (index | list) as listed / / object form operation (case by case) System.out.println (id + "\ t" + name + "\ t" + sex + "\ t" + date);} statement.close (); connection.close ();}} 6, SQL injection

SQL injection: is to use some systems to maliciously attack the database by injecting illegal SQL statement segments or commands into the user input data without sufficient inspection of the user input data.

For example, the following code implements the injection problem (which is the case with Statement, so it is not used in the actual development process)

Create table admit (name varchar (32), password varchar (32)); insert into admit values ('tom','1234'); select * from admit where name =' tom' and password = '1234'; # output tom 123'if someone inputs name as 1'or password as or'1', then select becomes select * from admit where name ='1' or 'and password =' or'1' ='1' # where'1' ='1' is always established. 7. Preprocessing query

Using PreparedStatement instead of Statement avoids the injection problem by passing in * *? * * instead of splicing (the PreparedStatement interface inherits the Statement interface)

Benefits of PreparedStatement

No longer use + concatenation sql statements to reduce syntax errors

Effectively solve the problem of sql injection!

The compilation times are greatly reduced and the efficiency is high.

Public class jdbc04 {public static void main (String [] args) throws Exception {Scanner scanner = new Scanner (System.in); System.out.print ("Please enter user name:"); String name = scanner.nextLine (); System.out.print ("Please enter password:"); String pwd = scanner.nextLine (); Properties properties = new Properties () Properties.load (new FileInputStream ("src\\ mysql.properties"); String user = properties.getProperty ("user"); String password = properties.getProperty ("password"); String url = properties.getProperty ("url"); String driver = properties.getProperty ("driver"); Class.forName (driver); Connection connection = DriverManager.getConnection (url, user, password); / / Statement statement = connection.createStatement () / / preparedStatement is the object of the PreparedStatement implementation class PreparedStatement preparedStatement = connection.prepareStatement ("select `name`, `password`" + "from admit where name =? And password =? "); preparedStatement.setString (1 minute name); / /? Subscript 1 starts with preparedStatement.setString (2jiggle PWD); ResultSet resultSet = preparedStatement.executeQuery (); if (resultSet.next ()) System.out.println ("login successful"); else System.out.println ("login failed"); preparedStatement.close (); connection.close () Insert, update, delete public class jdbc05 {public static void main (String [] args) throws Exception {Scanner scanner = new Scanner (System.in); System.out.print ("Please enter user name:"); String name = scanner.nextLine (); System.out.print ("Please enter password:"); String pwd = scanner.nextLine (); Properties properties = new Properties () Properties.load (new FileInputStream ("src\\ mysql.properties"); String user = properties.getProperty ("user"); String password = properties.getProperty ("password"); String url = properties.getProperty ("url"); String driver = properties.getProperty ("driver"); Class.forName (driver); Connection connection = DriverManager.getConnection (url, user, password) / / add String sql1 = "insert into admit values (?)"; / / modify String sql2 = "update admit set name =? Where name =? And password =? "; / / Delete String sql3 =" delete from admit where name =? And password =? "; PreparedStatement preparedStatement = connection.prepareStatement (sql3); / / preparedStatement.setString (1 plas name +" plas "); / /? The subscript starts at 1 / preparedStatement.setString (2maginame); / / preparedStatement.setString (3maginname); preparedStatement.setString (1magnamename); preparedStatement.setString (2mempwd); int rows = preparedStatement.executeUpdate (); if (rows > 0) System.out.println ("operation succeeded"); else System.out.println ("operation failed"); preparedStatement.close () Connection.close ();}} 8, tool class development

Because in the database operation, some steps are repeated, such as connecting, closing resources and so on.

Tool class

Package utils;import java.sql.*;import java.io.FileInputStream;import java.util.Properties;public class JDBCUtils {private static String user; / / username private static String password; / / password private static String url; / / connect to the url private static String driver; / / driver / / static code block of the database for line initialization static {try {Properties properties = new Properties () Properties.load (new FileInputStream ("src\\ mysql.properties"); user = properties.getProperty ("user"); password = properties.getProperty ("password"); url = properties.getProperty ("url"); driver = properties.getProperty ("driver") } catch (Exception e) {/ / during the actual development process (change the compilation exception to run exception, the user can catch the exception or handle it by default) throw new RuntimeException (e);}} / / connect public static Connection getConnection () {try {return DriverManager.getConnection (url,user,password) } catch (SQLException e) {throw new RuntimeException (e);} / / close the resource public static void close (ResultSet set, Statement statement,Connection connection) {try {if (set! = null) set.close (); if (statement! = null) statement.close (); if (connection! = null) connection.close () } catch (SQLException e) {throw new RuntimeException (e);}

Application:

Public class JdbcUtilsTest {@ Test / / Test the select operation public void testSelect () {Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try {/ / get the connection connection = JDBCUtils.getConnection (); / / set sql String sql = "select * from actor where id =?" / / create PreparedStatement preparedStatement = connection.prepareStatement (sql); / / place assignment preparedStatement.setInt (1Power2); / / execute resultSet = preparedStatement.executeQuery (); while (resultSet.next ()) {/ * you can also write int id = resultSet.getInt ("id") String name = resultSet.getString ("name"); String sex = resultSet.getString ("sex"); Date date = resultSet.getDate ("borndate"); String phone = resultSet.getString ("phone"); * / int id = resultSet.getInt (1); String name = resultSet.getString (2) String sex = resultSet.getString (3); Date date = resultSet.getDate (4); String phone = resultSet.getString (5); System.out.println (id + "\ t" + name + "\ t" + sex + "\ t" + date + "\ t" + phone);} catch (SQLException e) {e.printStackTrace () } finally {JDBCUtils.close (resultSet, preparedStatement, connection);} @ Test / / Test DML operation public void testDML () {Connection connection = null; PreparedStatement preparedStatement = null; try {/ / get the connection connection = JDBCUtils.getConnection () / / set sql String sql = "update actor set name =?, sex =? Where id =? "; / / create PreparedStatement preparedStatement = connection.prepareStatement (sql); / / placeholder assignment preparedStatement.setString (1," sxy "); preparedStatement.setString (2," male "); preparedStatement.setInt (3,2); / / execute preparedStatement.executeUpdate () } catch (SQLException e) {e.printStackTrace ();} finally {JDBCUtils.close (null, preparedStatement, connection);} 9, JDBC transaction public class Jdbc06 {public static void main (String [] args) {Connection connection = null; PreparedStatement preparedStatement = null; try {connection = JDBCUtils.getConnection (); connection.setAutoCommit (false) / / turn off autocommit (open transaction) / / the first action String sql = "update actor set phone = phone-10 where id = 2"; preparedStatement = connection.prepareStatement (sql); preparedStatement.executeUpdate (); / / int I = 1 where id 0; exception / / second action sql = "update actor set phone = phone + 10 where id = 1" PreparedStatement = connection.prepareStatement (sql); preparedStatement.executeUpdate (); / / commit transaction connection.commit ();} catch (Exception e) {System.out.println ("cancel sql service if there is an exception"); try {connection.rollback () / / rollback to the place where the transaction started} catch (SQLException throwables) {throwables.printStackTrace ();} e.printStackTrace ();} finally {JDBCUtils.close (null, preparedStatement, connection) 10. Batch public class Jdbc07 {@ Test / / ordinary processing 5000 insert data execution time 169839 public void test01 () {Connection connection = null; PreparedStatement preparedStatement = null; try {connection = JDBCUtils.getConnection (); String sql = "insert into actor (id, `name`, sex) values (?,?, 'male')" PreparedStatement = connection.prepareStatement (sql); long begin = System.currentTimeMillis (); for (int I = 0; I

< 5000; i++) { preparedStatement.setString(1, 3 + i + ""); preparedStatement.setString(2, "sxy" + (i + 1)); preparedStatement.executeUpdate(); } long end = System.currentTimeMillis(); System.out.println(end - begin); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(null, preparedStatement, connection); } } @Test //批处理 执行时间429 public void test02() { Connection connection = null; PreparedStatement preparedStatement = null; try { connection = JDBCUtils.getConnection(); String sql = "insert into actor(id,`name`,sex) values (?,?,'男')"; preparedStatement = connection.prepareStatement(sql); long begin = System.currentTimeMillis(); for (int i = 0; i < 5000; i++) { preparedStatement.setString(1, 3 + i + ""); preparedStatement.setString(2, "sxy" + (i + 1)); //将sql语句加入批处理包中 preparedStatement.addBatch(); /* preparedStatement.addBatch()在底层把每一条数据加入到ArrayList 执行过程:检查本条sql中的语法问题 ->

Add this sql statement to ArrayList-> execute a batch every 1000: reduce the number of compilations and the number of runs Greatly improved efficiency also needs to add url to the properties configuration file with? rewriteBatchedStatements=true url=jdbc:mysql://localhost:3306/test_table?rewriteBatchedStatements=true * / / when there are 1000 entries Processing if ((I + 1)% 1000 = = 0) {preparedStatement.executeBatch () / / empty batch package preparedStatement.clearBatch ();}} long end = System.currentTimeMillis (); System.out.println (end-begin);} catch (Exception e) {e.printStackTrace ();} finally {JDBCUtils.close (null, preparedStatement, connection) 11. Database connection pool

Because there are many users to connect to the database, and the number of database connections is limited, and even the connection and closure is time-consuming, so the introduction of database connection pool can solve this problem very well. The following is the time it takes to connect to a database and close 5000 times in 6249 milliseconds, which can be sent for a relatively long time.

Public class ConQuestion {public static void main (String [] args) {/ / see how long it will take to close connection long start = System.currentTimeMillis (); System.out.println ("start connection."); for (int I = 0; I)

< 5000; i++) { //使用传统的jdbc方式，得到连接 Connection connection = JDBCUtils.getConnection(); //做一些工作，比如得到PreparedStatement ，发送sql //.......... //关闭 JDBCUtils.close(null, null, connection); } long end = System.currentTimeMillis(); System.out.println("传统方式5000次 耗时=" + (end - start));//传统方式5000次 耗时=6249 }}11.1 数据库连接池基本介绍 预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时，只需从"缓冲池"中取出一个，使用完毕之后再放回去。 数据库连接池负责分配，管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。 当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。 11.2 JDBC的数据库连接池使用 JDBC的数据库连接池使用javax.sql.DataSource来表示，DataSource只是一个接口，该接口通常由第三方提供实现。 11.3 数据库连接池的种类 C3P0 数据库连接池，速度相对较慢，稳定性不错(hibernate，spring)。(用的较多) DBCP数据库连接池，速度相对c3p0较快，但不稳定。 Proxool数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点。 BoneCP 数据库连接池，速度快。 Druid (德鲁伊)是阿里提供的数据库连接池，集DBCP，C3P0，Proxool优点于身的数据库连接池。(应用最广) 11.4 C3P0连接池 利用C3P0连接池再次尝试连接5000次数据库 可以发现耗时方式一仅仅花了456毫秒，第二种通过配置文件操作也是花了419毫秒差不多的时间，值得说的是这个连接池连接配置文件不能是我们自己写，官方有给定的模板(c3p0.config.xml)。 public class C3P0_ { @Test //方式一: 相关参数,在程序中指定user,url,password等 public void testC3P0_1() throws Exception { //创建一个数据源对象 ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); //通过配合文件获取相关连接信息 Properties properties = new Properties(); properties.load(new FileInputStream("src\\mysql.properties")); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String url = properties.getProperty("url"); String driver = properties.getProperty("driver"); //给数据源(comboPooledDataSource)设置相关参数 //连接管理是由comboPooledDataSource(连接池)来管理的 comboPooledDataSource.setDriverClass(driver); //设置驱动 comboPooledDataSource.setJdbcUrl(url); comboPooledDataSource.setUser(user); comboPooledDataSource.setPassword(password); //初始化数据源的连接数 comboPooledDataSource.setInitialPoolSize(10); //数据库连接池最大容量,如果还有连接请求,那么就会将该请求放入等待队列中 comboPooledDataSource.setMaxPoolSize(50); //测试连接池的效率, 测试对mysql 5000次操作 long start = System.currentTimeMillis(); for (int i = 0; i < 5000; i++) { //getConnection()这个方法就是重写了DataSource接口的方法 Connection connection = comboPooledDataSource.getConnection(); connection.close(); } long end = System.currentTimeMillis(); //c3p0 5000连接mysql 耗时=456 System.out.println("c3p0 5000连接mysql 耗时=" + (end - start)); comboPooledDataSource.close(); } //第二种方式 使用配置文件模板来完成 //将C3P0 提供的 c3p0.config.xml 拷贝到 src目录下 //该文件指定了连接数据库和连接池的相关参数 @Test public void testC3P0_02() throws SQLException { ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("sxy"); //测试5000次连接mysql long start = System.currentTimeMillis(); for (int i = 0; i < 5000; i++) { Connection connection = comboPooledDataSource.getConnection(); connection.close(); } long end = System.currentTimeMillis(); //c3p0的第二种方式(5000) 耗时=419 System.out.println("c3p0的第二种方式(5000) 耗时=" + (end - start)); }}11.5 Druid连接池 在使用Druid连接池连接数据库500000次耗时643毫秒,而C3P0500000次连接耗时2373毫秒,很显然Druid连接速度更快。 public class Druid_ { @Test public void testDruid() throws Exception { //1.加入Druid jar包 //2.加入 配置文件 druid.properties 放到src目录下 //3.创建Properties对象 Properties properties = new Properties(); properties.load(new FileInputStream("src\\druid.properties")); //4.创建一个指定参数的数据库连接池 DataSource dataSource = DruidDataSourceFactory.createDataSource(properties); long start = System.currentTimeMillis(); for (int i = 0; i < 500000; i++) { Connection connection = dataSource.getConnection(); connection.close(); } long end = System.currentTimeMillis(); //Druid的500000次创建 耗时=643 System.out.println("Druid的500000次创建 耗时=" + (end - start)); }} 对应的工具类 public class JDBCUtilsByDruid { private static DataSource ds; //在静态代码块完成 ds初始化 static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src\\druid.properties")); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //编写getConnection方法 public static Connection getConnection() throws SQLException { return ds.getConnection(); } //关闭连接:在数据库连接池技术中,close不是真的断掉连接,而是把使用的Connection对象放回连接池 public static void close(ResultSet resultSet, Statement statement, Connection connection) { try { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (connection != null) connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } }} 使用工具类： public class TestUtilsByDruid { @Test public void testSelect() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { //得到连接 connection = JDBCUtilsByDruid.getConnection(); System.out.println(connection.getClass()); //connection 的运行类型 class com.alibaba.druid.pool.DruidPooledConnection //设置sql String sql = "select * from actor where id = ?"; //创建PreparedStatement preparedStatement = connection.prepareStatement(sql); //占位赋值 preparedStatement.setInt(1, 2); //执行 resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt(1); String name = resultSet.getString(2); String sex = resultSet.getString(3); Date date = resultSet.getDate(4); String phone = resultSet.getString(5); System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtilsByDruid.close(resultSet, preparedStatement, connection); } }}12、Apache-DBUtils 由于resultSet存放数据集合，在connection关闭时，resultSet结果集无法使用。所以为了使用这些数据，也有JDBC官方提供的文件Apache-DBUtils来存放数据。 12.1 ArrayList模拟 ArrayList模拟Apache-DBUtils Actor类 用来保存Actor表中的数据用的。 public class Actor { //Javabean, POJO, Domain对象 private Integer id; private String name; private String sex; private Date borndate; private String phone; public Actor() { //一定要给一个无参构造器[反射需要] } public Actor(Integer id, String name, String sex, Date borndate, String phone) { this.id = id; this.name = name; this.sex = sex; this.borndate = borndate; this.phone = phone; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBorndate() { return borndate; } public void setBorndate(Date borndate) { this.borndate = borndate; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "\nActor{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", borndate=" + borndate + ", phone='" + phone + '\'' + '}'; }} 用ArrayList来存放数据 public class LikeApDB { @Test public /*也可以返回ArrayList*/void testSelectToArrayList() { Connection connection = null; String sql = "select * from actor where id >

=?; PreparedStatement preparedStatement = null; ResultSet resultSet = null; ArrayList list = new ArrayList (); try {connection = JDBCUtilsByDruid.getConnection (); System.out.println (connection.getClass ()); preparedStatement = connection.prepareStatement (sql); preparedStatement.setInt (1,1); resultSet = preparedStatement.executeQuery () While (resultSet.next ()) {int id = resultSet.getInt ("id"); String name = resultSet.getString ("name"); / / getName () String sex = resultSet.getString ("sex"); / / getSex () Date borndate = resultSet.getDate ("borndate"); String phone = resultSet.getString ("phone") / / encapsulate the resultSet records into Actor objects and put them into the list collection list.add (new Actor (id, name, sex, borndate, phone);} System.out.println ("list collection data =" + list) For (Actor actor: list) {System.out.println ("id=" + actor.getId () + "\ t" + actor.getName ());}} catch (SQLException e) {e.printStackTrace ();} finally {/ / close resource JDBCUtilsByDruid.close (resultSet, preparedStatement, connection) } / / because there is no association between ArrayList and connection, the collection can be reused. / / return list;}} 12.2 Apache-DBUtils

Basic introduction

Commons-dbutils is an open source JDBC tool class library provided by Apache. It encapsulates JDBC. Using dbutils can greatly simplify the workload of jdbc coding.

DbUtils class

QueryRunner class: this class encapsulates the execution of SQL and is thread-safe. Can add, delete, change, check, batch processing

Use the QueryRunner class to implement the query.

ResultSetHandler interface: this interface is used to process java.sql.ResultSet and convert data into another form as required.

Application example

Use Apache-DBUtils tool + database connection pool (Druid) to complete the addition, deletion, modification and query of a table.

Package datasourse;import ApDB.Actor;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.ScalarHandler;import org.junit.jupiter.api.Test;import java.sql.Connection;import java.sql.SQLException;import java.util.List;public class DBUtils_Use {@ Test / / query multiple data public void testQueryMany () throws Exception {/ / 1. Get the Druid Connection connection = JDBCUtilsByDruid.getConnection (); / / 2. Using DBUtils classes and interfaces, first introduce the DBUtils jar file and put it in the src directory / / 3. Create QueryRunner QueryRunner queryRunner = new QueryRunner (); / / 4. Execute the corresponding method and return the ArrayList result set String sql = "select * from actor where id > =?"; / / String sql = "select id, `name` from actor where id > =?" / * (1) the query method is to execute the sql statement Get resultSet-- encapsulated into-- > ArrayList collection (2) return collection (3) connection: connect (4) sql: sql statement executed (5) new BeanListHandler (Actor.class): encapsulate resultSet-> Actor object-> into ArrayList underlying layer use reflection mechanism to get properties of Actor class And then encapsulate (6) 1 is for the sql statement? Assignment, can have multiple values, because it is a variable parameter Object... The resultSet obtained at the bottom of params (7) will be closed in query, and PreparedStatement * / List query = queryRunner.query (connection, sql, new BeanListHandler (Actor.class), 1) will be closed; / * * queryRunner.query method source code analysis * public T query (Connection conn, String sql, ResultSetHandler rsh, Object...) Params) throws SQLException {* PreparedStatement stmt = null;// define PreparedStatement * ResultSet rs = null;// receive the returned ResultSet * Object result = null;// return ArrayList * * try {* stmt = this.prepareStatement (conn, sql); / / create PreparedStatement * this.fillStatement (stmt, params) / / A pair of sql? Assign * rs = this.wrap (stmt.executeQuery ()); / / execute sql and return resultset * result = rsh.handle (rs); / / returned resultset-- > arrayList [result] [handle incoming class objects using reflection] *} catch (SQLException var33) {* this.rethrow (var33, sql, params) *} finally {* try {* this.close (rs); / / close resultset *} finally {* this.close ((Statement) stmt) / / close preparedstatement object * return result; * * / for (Actor actor: query) {System.out.print (actor);} JDBCUtilsByDruid.close (null,null,connection) } @ Test / / query single record public void testQuerySingle () throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection (); QueryRunner queryRunner = new QueryRunner (); String sql = "select * from actor where id =?"; / / it is known that the query is in a single line, so use BeanHandler to return a corresponding object Actor query = queryRunner.query (connection, sql, new BeanHandler (Actor.class), 2) System.out.print (query); JDBCUtilsByDruid.close (null,null,connection);} @ Test / / query single row, single column (some information) returns an Object object public void testQuerySingleObject () throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection (); QueryRunner queryRunner = new QueryRunner (); String sql = "select `name` from actor where id =?" / / it is known that the query is a single row and single column, so use BeanHandler and return an Object Object query = queryRunner.query (connection, sql, new ScalarHandler (), 1); System.out.println (query); JDBCUtilsByDruid.close (null,null,connection);} @ Test / / demonstrate the DML operation (insert,update,delete) public void testDML () throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection () QueryRunner queryRunner = new QueryRunner (); / / String sql = "update actor set phone =? Where id =? "; / / int affectedRow = queryRunner.update (connection, sql," 110,2); String sql = "insert into actor values (?)"; int affectedRow = queryRunner.update (connection, sql, 3, "xhj", "female", "2000-05-26", "110"); / / String sql = "delete from actor where id =?" / / int affectedRow = queryRunner.update (connection, sql, 5004); System.out.println (affectedRow > 0? "OK": "NO"); JDBCUtilsByDruid.close (null,null,connection);}} 13, BasicDao

Introduce a problem

The SQL statement is fixed and cannot be passed in through parameters. Its versatility is not good, and it needs to be improved to facilitate the implementation of additions, deletions, modifications and queries.

For select operations, if there is a return value, the return type cannot be fixed, so you need to use generics

There will be a lot of tables in the future, and the business requirements are so complex that it is impossible to rely on a single JAVA class.

So in the actual development, there is also a solution-BasicDao

13.1 the BasicDAO class public class BasicDAO {/ / generic specifies the specific type private QueryRunner queryRunner = new QueryRunner (); / / develops a generic DML for any table public int update (String sql,Object...) Parameter) {Connection connection = null; try {connection = JDBCUtilsByDruid.getConnection (); return queryRunner.update (connection, sql, parameter);} catch (SQLException e) {throw new RuntimeException (e); / / convert compilation exceptions to run exceptions, which can be caught or thrown} finally {JDBCUtilsByDruid.close (null,null,connection) }} / * * returns multiple objects (multiple row results) * * @ param sql sql statement, can it exist? * @ param clazz pass in the class object of a class, such as Actor.class * @ param parameter? You can have multiple * @ return to return the corresponding ArrayList collection * / public List QueryMultiply (String sql,Class clazz, Object...) according to the similar Actor.class type. Parameter) {Connection connection = null; try {connection = JDBCUtilsByDruid.getConnection (); return queryRunner.query (connection,sql,new BeanListHandler (clazz), parameter);} catch (SQLException e) {throw new RuntimeException (e); / / convert compilation exceptions to run exceptions, which can be caught or thrown} finally {JDBCUtilsByDruid.close (null,null,connection) }} / / returns a single object (single row of data) public T querySingle (String sql,Class clazz,Object... Parameter) {Connection connection = null; try {connection = JDBCUtilsByDruid.getConnection (); return queryRunner.query (connection,sql,new BeanHandler (clazz), parameter);} catch (SQLException e) {throw new RuntimeException (e); / / convert compilation exceptions to run exceptions, which can be caught or thrown} finally {JDBCUtilsByDruid.close (null,null,connection) }} / / returns a single property of a single object (single column in a single row) public Object queryScalar (String sql,Object... Parameter) {Connection connection = null; try {connection = JDBCUtilsByDruid.getConnection (); return queryRunner.query (connection,sql,new ScalarHandler (), parameter);} catch (SQLException e) {throw new RuntimeException (e); / / convert compilation exceptions to run exceptions, which can be caught or thrown} finally {JDBCUtilsByDruid.close (null,null,connection) The class public class Actor {/ / Javabean, POJO, Domain object private Integer id; private String name; private String sex; private Date borndate; private String phone; public Actor () {/ / must be given to a nonparametric constructor [reflection needs]} public Actor (Integer id, String name, String sex, Date borndate, String phone) {this.id = id This.name = name; this.sex = sex; this.borndate = borndate; this.phone = phone;} public Integer getId () {return id;} public void setId (Integer id) {this.id = id;} public String getName () {return name;} public void setName (String name) {this.name = name;} public String getSex () {return sex } public void setSex (String sex) {this.sex = sex;} public Date getBorndate () {return borndate;} public void setBorndate (Date borndate) {this.borndate = borndate;} public String getPhone () {return phone;} public void setPhone (String phone) {this.phone = phone } @ Override public String toString () {return "\ nActor {" + "id=" + id + ", name='" + name +'\'+ ", sex='" + sex +'\'+ ", borndate=" + borndate + ", phone='" + phone +'\'+'}';}}

The ActorDAO class inherits the BasicDAO class, and there can be many such classes.

The public class ActorDAO extends BasicDAO {} 13.3 test class public class TestDAO {@ Test// tests ActorDAO's operation on the actor table public void testActorDAO () {ActorDAO actorDAO = new ActorDAO (); / / 1. Query multiple lines List actors = actorDAO.QueryMultiply ("select * from actor where id > =?", Actor.class, 1); System.out.println (actors); / / 2. Query single line Actor actor = actorDAO.querySingle ("select * from actor where id =?", Actor.class, 1); System.out.println (actor); / / 3. Query single row single data Object o = actorDAO.queryScalar ("select name from actor where id =?", 1); System.out.println (o); / / 4.DML operation current demonstration update int affectedRow = actorDAO.update ("update actor set phone =? where id =?", "120,3"); System.out.println (affectedRow > 0? "OK": "NO")}} Thank you for reading this article carefully. I hope the article "sample Analysis of Database connection JDBC in Java" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.