JDBC and jdbc connect to the database

JDBC and jdbc connect to the database

I. JDBC
1. Overview
Java DataBase Connectivity-JAVA Data Connection Library is a java api used to execute SQL statements,
Provides unified access to multiple relational databases, which are composed of a group of classes and interfaces written in JAVA. JDBC provides a benchmark based on which you can build more advanced tools and interfaces and use database developers to write database applications.
2. JDBC principles
JDBC allows programmers to use fixed procedures and methods to operate different databases;
Core classes and interfaces:
Driver Interface -- used to identify databases;
Connection interface -- used to connect to a database session;
Statement interface -- SQL executor;
ResultSet interface -- result set of SQL Execution;

DriverManager class, used to operate the JDBC tool class. Through this class method, you can register the driver and obtain the connection.

Driver Interface (java. SQL)
Definition: public interface Driver
Common Methods:
Connection connect (String url, Properties info) throws SQLException {}: establish a Connection with the database through the specified url;
Connection interface (java. SQL)
Definition: public interface Connection extends Wrapper, AutoCloseable
Common Methods:
Void close () throws SQLException {}: close the resource
Statement createStatement () throws SQLException {}: Creates a Statement object to send SQL statements to the database.
PreparedStatement prepareStatement (String SQL) throws SQLException {}: returns a PerparedStatement object;

StateMent interface (java. SQL)
Definition: public interface Statement extends Wrapper, AutoCloseable
Common Methods:
Boolean execute (String SQL) throws SQLException {}: execute SQL;
ResultSet executeQuery (String SQL) throws SQLException {}: returns the query result set;
Int executeUpdate (String SQL) throws SQLException {}: execute a given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement (such as an SQL DDL statement) that does not return any content ).
ResultSet interface (java. SQL)
Definition: public interface ResultSet extends Wrapper, AutoCloseable
Common Methods:
Void close () throws SQLException {}: close the resource;
Boolean next () throws SQLException {}: move the cursor forward (down) a row from the current position.
Object getObject (String columnLabel) throws SQLException {}: obtains the value of the column specified in the current row of the ResultSet Object as an Object in Java programming language.
Object getObject (int columnIndex) throws SQLException {}: obtains the value of the column specified in the current row of the ResultSet Object as an Object in Java programming language.

DriverManager class (java. SQL)
Definition: public class DriverManager extends Object
Common Methods:
Public static void registerDriver (Driver driver) throws SQLException {}: register the given Driver with DriverManager. After JAVA1.6, it can be automatically registered without writing;
Public static Connection getConnection (String url, String user, String password) throws SQLException {}: attempts to establish a Connection to the given Database URL.

3. steps:
1) register the Driver. Let the java code identify the database. The real-time Driver interface is provided by the database manufacturer. the mysql database Driver class can be directly downloaded from the mysql official website, driver Class import project -- registerDriver () method of DriverManager class;
2) obtain the connection and create a session object: The getConnection (url, use, password) method of the DriverManager class;
3) obtain the SQL executor: createStatement () method of the Connection interface;
4) Write SQL statements and execute the executeUpdate () or executeQuery () Methods of the Statement interface;
5) processing result set (mainly query results );
6) release the resource. RESULTSET/STATEMENT/CONNECTION/close the resource in this order. The close () method is used;

4. PreparedStatement interface (java. SQL) Statement subclass
Note: An instantiated object of this interface can only execute an SQL statement once !!
Definition: public interface PreparedStatement extends Statement
Common Methods:
Boolean execute () throws SQLException {}: returns a boolean value after the SQL statement is executed. Generally, this parameter is not required;
ResultSet executeQuery () throws SQLException {}: returns the query result set;
Int executeUpdate () throws SQLException {}: Execute an SQL statement in this PreparedStatement object. The statement must be a Data Manipulation Language (DML) statement, such as INSERT, UPDATE, or DELETE statements, or SQL statements without returned content, such as DDL statements.
Void setObject (int parameterIndex, Object x) throws SQLException {}: Use the given Object to set the value of the specified parameter. ParameterIndex: The placeholders in the SQL statement, starting from 1. X: indicates the value to be assigned;

5. Code Demonstration: the following code is implemented in Eclipse and the jar package needs to be imported.
Code 1:

1/* 2 Use JDBC to query database data 3 1: Register driver 4 2: Create Session Object (road) 5 3: Get SQL performer 6 4: Write SQL and execute 7 5: processing result set 8 6: release resources (result set, SQL Execution, Session Object) 9 */10 import java. SQL. connection; 11 import java. SQL. driverManager; 12 import java. SQL. resultSet; 13 import java. SQL. statement; 14 15 public class Demo02 {16 public static void main (String [] args) throws Exception {17 // use the reflection loading class to register the driver-fixed path. Remember "com. mysql. jdbc. driver "18 Class. forName ("com. m Ysql. jdbc. Driver "); 19 // create a Session Object-a fixed local mysql path. Remember" jdbc: mysql: // mydb? UseSSL = false "20 Connection c = DriverManager. getConnection (" jdbc: mysql: // mydb? UseSSL = false "," root "," root "); // pay attention to the SSL warning. If you do not want to display it, add "? UseSSL = false "21 // create performer 22 Statement st = c. createStatement (); 23 // write an SQL statement and run 24 String SQL = "SELECT * FROM category"; 25 ResultSet set = st.exe cuteQuery (SQL ); 26 // processing result set 27 while (set. next () {28 Object cid = set. getObject ("cid"); 29 Object cname = set. getObject ("cname"); 30 System. out. println (cid + "----" + cname); 31} 32 // close the resource -- order: ResultSet/Statement/Connection 33 set. close (); 34 st. close (); 35 c. close (); 36} 37}

Code 2: Custom JDBC tool class-thoughts on the idea of object-oriented Encapsulation

1 import java. SQL. connection; 2 import java. SQL. driverManager; 3 import java. SQL. resultSet; 4 import java. SQL. SQLException; 5 import java. SQL. statement; 6/* 7 * When JDBC is used, registration, connection retrieval, and resource shutdown are fixed modes, can be encapsulated into class 8 **/9 public class MyJDBCUtils {10 // define the member variable -- because the Mysql Driver class path-Database URL-user name-password is generally fixed, therefore, you can set it as a member variable and add final to modify 11 private static final String path = "com. mysql. jdbc. driver "; 12 private static final String ur L = "jdbc: mysql: // localhost: 3306/mydb? UseSSL = false "; 13 private static final String user =" root "; 14 private static final String password =" root "; 15 // registration method, because only one registration is performed, so use static code block 16 static {17 try {18 Class. forName (path); 19} catch (ClassNotFoundException e) {20 e. printStackTrace (); 21 throw new RuntimeException ("Remember to pilot the package ~ "); 22} 23} 24 // get the Connection, return the Connection class instance object 25 public static Connection getConnection () {26 try {27 Connection c = DriverManager. getConnection (url, user, password); 28 return c; 29} catch (SQLException e) {30 e. printStackTrace (); 31 throw new RuntimeException ("is the path correct? Is the database created? Is the password correct? "); 32} 33} 34 // close the resource -- ordered Result/statement/connection 35 public static void close (ResultSet r, Statement st, Connection c) {36 if (c! = Null) {37 try {38 c. close (); 39} catch (SQLException e) {40 e. printStackTrace (); 41 throw new RuntimeException ("connection close exception! "); 42} 43} 44 45 if (st! = Null) {46 try {47 st. close (); 48} catch (SQLException e) {49 e. printStackTrace (); 50 throw new RuntimeException ("execution close exception! "); 51} 52} 53 54 if (r! = Null) {55 try {56 r. close (); 57} catch (SQLException e) {58 e. printStackTrace (); 59 throw new RuntimeException ("An error occurred while disabling the result set! "); 60} 61} 62} 63}

Code 3: Use a custom tool class to complete CRUD

1 import huguangqin.com. cnblogs. myJDBCUtils. myJDBCUtils; 2 import java. SQL. connection; 3 import java. SQL. resultSet; 4 import java. SQL. SQLException; 5 import java. SQL. statement; 6/* 7 * use the tool class to complete CRUD 8 **/9 public class Demo01 {10 public static void main (String [] args) throws SQLException {11 // register the driver-tool class and obtain the connection-tool class-(no registration is required. Because the static code block is set in the class, the class automatically registers for obtaining the connection call) 12 Connection c = MyJDBCUtils. getConnection (); 13 14 // get the performer Statement 15 Statement st = c. createStatement (); 16 17 // write the SQL statement, and run 18 // INSERT data 19 String insertSQL = "INSERT INTO category (cid, cname) VALUES (7, 'volumes '), (8, 'steamed stuffed bun ') "; 20 // execute insert 21 int I = st.exe cuteUpdate (insertSQL); 22 // query data 23 String querySQL =" SELECT * FROM category "; 24 // execute query 25 ResultSet set = st.exe cuteQuery (querySQL); 26 // process result set-ResultSet 27 while (set. next () {28 Object cid = set. getObject ("cid"); 29 Object cname = set. getObject ("cname"); 30 System. out. println (cid + "=" + cname); 31} 32 // close the resource-tool Class 33 MyJDBCUtils. close (set, st, c); 34} 35}

Code 4: // obtain the PreparedStatement object for Connection and pre-process each statement of the SQL object to prevent SQL injection attacks.

1 import huguangqin.com. cnblogs. myJDBCUtils. myJDBCUtils; 2 import java. SQL. connection; 3 import java. SQL. preparedStatement; 4 import java. SQL. resultSet; 5 import java. SQL. SQLException; 6 7 public class PreparedStatementDemo {8 public static void main (String [] args) throws SQLException {9 // get the connection using the custom tool class, register the driver 10 Connection c = MyJDBCUtils. getConnection (); 11 // use PreparedStatement to insert three rows of data to the table 12 String insertSQL = "insert into user (name, password) VALUES (?,?) "; 13 // get PreparedStatement object for Connection 14 PreparedStatement pst = c. prepareStatement (insertSQL); 15 // set the actual parameter 16 pst. setObject (1, "a"); 17 pst. setObject (2, "a"); 18 // execute (one instantiated object of PreparedStatement can only execute SQL once) 19 int I = pst.exe cuteUpdate (); 20 21 // query 22 String querySQL = "SELECT * from user where name =? "; 23 // get PreparedStatement object for Connection 24 PreparedStatement pst1 = c. prepareStatement (querySQL); 25 pst1.setObject (1, "a"); 26 ResultSet set = pst1.executeQuery (); 27 // processing result set 28 while (set. next () {29 Object name = set. getObject ("name"); 30 Object password = set. getObject ("password"); 31 System. out. println (name + "====" + password); 32} 33} 34} 35