JDBC introduction

The full name of JDBC is java data base connectivity, which can provide unified access to a variety of databases. JDBC is a set of database access programming interface developed by sun, which is a SQL level API. It is written by java language, so it has good cross platform characteristics. The database application written by JDBC can run on any platform that supports Java, without having to write different applications on different platforms.

JDBC programming steps

(1) load driver:

Download the driver package: http://dev.mysql.com/downloads/connector/j/

Extract and get the jar file. Copy the file to the Java Resources/Libraries / Java project directory, → buildpath.

(2) get database connection

(3) create Statement object:

(4) send SQL command to database

(5) processing the returned results of the database (ResultSet class)

 1 package com.baidu.emp.jdbcTest;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import com.mysql.jdbc.Driver;
 9 /**
10  * Start using jdbc to connect to the database
11  * @author Admin
12  *
13  */
14 public class Test001 {
15 
16     public static void main(String[] args) throws Exception {
17 
18         /**
19          * Load driver
20          */
21         // Method 1:
22         /*
23          * import java.sql.DriverManager; import com.mysql.jdbc.Driver;
24          */
25         // Driver driver = new Driver();
26         // DriverManager.registerDriver(driver);
27 
28         // Method two:(Recommended use)
29         Class.forName("com.mysql.jdbc.Driver");
30 
31         /**
32          * create link
33          */
34         String url = "jdbc:mysql://localhost:3306/testjdbc";
35         String user = "root";
36         String password = "root";
37         Connection connection = DriverManager.getConnection(url, user, password);
38 
39         // Establish statement object
40         Statement statement = connection.createStatement();
41 
42         /**
43          * Execute SQL, get result set
44          */
45         String sql = "select * from test01";
46         ResultSet result = statement.executeQuery(sql);
47 
48         // Traversal result set
49         while (result.next()) {
50             String name = result.getString("name");
51             int id = result.getInt("id");
52             System.out.println(name + "\t" + id);
53         }
54 
55         /**
56          * Close links, release resources
57          */
58         result.close();
59         statement.close();
60         connection.close();
61     }
62 }

Prevent SQL injection from using prepareStatement instead

 1 package com.boya.emp.jdbcTest;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 /**
 8  * SQL Injection, precompiling with prepareStatement object
 9  * @author Admin
10  *
11  */
12 public class Test002 {
13 
14     public static void main(String[] args) throws Exception {
15 
16         /**
17          * Load driver
18          */
19         Class.forName("com.mysql.jdbc.Driver");
20 
21         /**
22          * create link
23          */
24         String url = "jdbc:mysql://localhost:3306/testjdbc";
25         String user = "root";
26         String password = "root";
27         Connection connection = DriverManager.getConnection(url, user, password);
28 
29         // write SQL 
30         String sql = "select * from test01 where id = ?";
31         //Establish statement Object, precompiling
32         PreparedStatement statement = connection.prepareStatement(sql);
33         //Setting parameters
34         statement.setInt(1, 2);
35         /**
36          * Execute SQL, get result set
37          */
38         ResultSet result = statement.executeQuery();
39 
40         // Traversal result set
41         while (result.next()) {
42             String name = result.getString("name");
43             int id = result.getInt("id");
44             System.out.println(name + "\t" + id);
45         }
46 
47         /**
48          * Close links, release resources
49          */
50         result.close();
51         statement.close();
52         connection.close();
53     }
54 } 

Optimize the code, set the configuration file and tool class, and add or delete the query

Add configuration files to facilitate database modification and user login...

Jdbc.properties (configuration file name)

1 driverName=com.mysql.jdbc.Driver
2 url=jdbc:mysql://localhost:3306/testjdbc
3 userName=root
4 password=root

Note that there can be no spaces or quotes in the middle when writing configuration files

Tool class: enhanced code reusability

  1 package com.baidu.emp.utils;
  2 
  3 import java.io.InputStream;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 import java.util.Properties;
 10 
 11 import org.junit.Test;
 12 
 13 
 14 
 15 public class JdbcUtils {
 16 
 17     static String driverClassName;
 18     static String url;
 19     static String user;
 20     static String password;
 21 
 22     static {
 23         // Create profile object
 24         Properties properties = new Properties();
 25         // Load profile input stream
 26         InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
 27         // Reload profile
 28         try {
 29             properties.load(inputStream);
 30             // Get the value of the profile
 31             driverClassName = properties.getProperty("driverName");
 32             url = properties.getProperty("url");
 33             user = properties.getProperty("userName");
 34             password = properties.getProperty("password");
 35             Class.forName(driverClassName);
 36 
 37         } catch (Exception e) {
 38             // Throw exception
 39             throw new RuntimeException(e);
 40         }
 41     }
 42 
 43     /**
 44      * Get connection
 45      */
 46     @Test
 47     public void testName() throws Exception {
 48         
 49         System.out.println(driverClassName);
 50     }
 51     public static Connection getConnection() {
 52         Connection connection = null;
 53         try {
 54             connection = DriverManager.getConnection(url, user, password);
 55         } catch (SQLException e) {
 56             // Throw exception
 57             throw new RuntimeException(e);
 58         }
 59         return connection;
 60     }
 61 
 62     /**
 63      * Close links, release resources
 64      */
 65     public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
 66 
 67         try {
 68             if (resultSet != null) {
 69                 resultSet.close();
 70             }
 71             resultSet = null; // Timely removal of garbage
 72             //Be careful not to make a dead cycle
 73             close(connection, statement);
 74         } catch (SQLException e) {
 75             throw new RuntimeException(e);
 76         }
 77 
 78     }
 79 
 80     /**
 81      * Add, delete, change and release resources
 82      */
 83     public static void close(Connection connection, PreparedStatement statement) {
 84 
 85         try {
 86             if (connection != null) {
 87                 connection.close();
 88             }
 89                 
 90             connection = null;
 91             if (statement != null) {
 92                 statement.close();
 93             }
 94             statement = null;
 95 
 96         } catch (SQLException e) {
 97             throw new RuntimeException(e);
 98         }
 99 
100     }
101 
102 }

Test addition, deletion, modification and query:

  1 package com.baidu.emp.jdbcTest;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 
  7 import org.junit.After;
  8 import org.junit.Before;
  9 import org.junit.Test;
 10 
 11 import com.baidu.emp.utils.JdbcUtils;
 12 
 13 /**
 14  * Using JDBC utils to connect database for adding, deleting, modifying and querying
 15  * 
 16  * @author Admin
 17  *
 18  */
 19 public class Test003 {
 20 
 21     // initialize value
 22     Connection connection = null;
 23     PreparedStatement statement = null;
 24     ResultSet result = null;
 25 
 26     @Before
 27     public void start() throws Exception {
 28         // create link
 29         connection = JdbcUtils.getConnection();
 30         System.out.println("create link");
 31     }
 32 
 33     @After
 34     public void end() throws Exception {
 35         // Close links
 36         JdbcUtils.close(connection, statement, result);
 37         System.out.println("Close links");
 38     }
 39     
 40     /**
 41      *insert data
 42      * @throws Exception
 43      */
 44     @Test
 45     public void add() throws Exception {
 46         String sql = "insert into test01 values(null,?)";
 47         statement = connection.prepareStatement(sql);
 48         statement.setString(1, "Li Si");
 49         int result = statement.executeUpdate();
 50         if (result!=0) {
 51             System.out.println("Add success");
 52         }
 53     }
 54     /**
 55      * Delete data
 56      * @throws Exception
 57      */
 58     @Test
 59     public void del() throws Exception {
 60         String sql = "delete from test01 where id =?";
 61         statement = connection.prepareStatement(sql);
 62         statement.setInt(1,3);
 63         int result = statement.executeUpdate();
 64         if (result!=0) {
 65             System.out.println("Delete successful");
 66         }
 67     }
 68     /**
 69      * Modifying data
 70      * @throws Exception
 71      */
 72     @Test
 73     public void change() throws Exception {
 74         String sql = "update test01 set name = ? where id = ?";
 75         statement = connection.prepareStatement(sql);
 76         statement.setString(1, "Zhang Fei");
 77         statement.setInt(2, 2);
 78         int result = statement.executeUpdate();
 79         if (result!=0) {
 80             System.out.println("Modified success");
 81         }
 82     }
 83     
 84     /**
 85      * Query all data
 86      * @throws Exception
 87      */
 88     @Test
 89     public void findAll() throws Exception {
 90         String sql = "select id , name from test01";
 91         statement = connection.prepareStatement(sql);
 92         result = statement.executeQuery();
 93         if (result.next()) {
 94             System.out.println("query was successful");
 95         }
 96     }
 97     
 98     /**
 99      * Condition query data
100      * @throws Exception
101      */
102     @Test
103     public void findOne() throws Exception {
104         String sql = "select id , name from test01 where id = ?";
105         statement = connection.prepareStatement(sql);
106         statement.setInt(1, 2);
107         result = statement.executeQuery();
108         if (result.next()) {
109             System.out.println("query was successful");
110         }
111     }
112 
113 }

I hope you will point out that there are mistakes. Thank you very much