Java self-learning path ----- jdbc _ Basics

JDBC (JavaDataBaseConnectivity): it facilitates applications to operate on database data through jdbc. basic steps: (pilot the driver into the database) {1. connect to the database first to obtain the object of the database (1. first load the database driver 2. obtain the connection through the basic information of the database. obtain operations through connection

JDBC (Java Data Base Connectivity): basic steps for applications to operate databases through jdbc: (pilot the driver into the database) {1. connect to the database first to obtain the object of the database (1. first load the database driver 2. obtain the connection through the basic information of the database. obtain operations through connection

JDBC (Java Data Base Connectivity): basic steps for applications to operate databases through jdbc: (pilot the driver into the database ){

1. connect to the database first to obtain the object of the database (1. first load the database driver 2. obtain the connection through the basic information of the database. obtain the statement object of the Operation database through a connection)

2. Then run the SQL statement {

Database url:Used to identify the location of the database. The program needs to use a url to tell the jdbc program to connect to the database:

Jdbc: mysql: // localhost: 3306/mydb? Parameter Name: Parameter Value

Protocol sub-Protocol HOST: Port Database Name

String url = "jdbc: // localhost: 3306/mydb"; // database address String user = "root"; // database username String password = "root "; // Database Password // The first method: // 1. first load the driver DriverManager. registerDriver (new com. mysql. jdbc. driver (); // 2. obtain the Connection conn1 = DriverManager through the driver. getConnection (url, user, password); // 3. get statement object Statement st1 = conn1.createStatement () through connection; // disadvantages: 1. the driver is registered twice: view the driver class. as you can know, this class has a constructor that automatically registers the class once in the driver when loading the class. There will be two driver objects // 2. the program imports the mysql driver, causing the program to depend on the mysql driver api. If the jar package is left, the program compilation will fail. // The second method is: // 1. first load the driver Class. forName ("com. mysql. jdbc. driver "); // 2. obtain the Connection conn2 = DriverManager through the driver. getConnection (url, user, password); // 3. obtain the statement object Statement st2 = conn2.createStatement () through a connection; // advantage: This method does not cause the drive object to appear repeatedly in the memory. In this mode, the program only uses one string, you do not need to rely on a specific driver.

Connection: represents a database Connection, used for client-to-database interaction.

1. createStatement (); Create a statement object for sending SQL statements to the database

2. prepareStatement (SQL); Create a preparestatement object that sends a pre-compiled SQL statement to the database

3. prepareCall (SQL); create the callableStatement object for executing the Stored Procedure

4. setAutoCommit (boolean B); Sets whether the transaction is automatically committed.

5. commit (); Submit the transaction on the connection

6. rollback (); roll back the transaction on this link

Statement: used to send SQL statements to the database

1. executeQuery (SQL); used to send query statements to the database. Returns the resultset object.

2. executeUpdate (SQL) is used to send update, insert, and delete statements to the database. Returns the number of rows that affect the data. If the value is greater than 0, the execution is successful.

3. execute (SQL); used to send arbitrary statements to the database. Whether execution is successful

4. addBatch (SQL); Put Multiple SQL statements in one batch

5. executeBatch (); sends a batch of SQL statements to the database

}

3. If it is a select SQL statement, the returned result set is ResultSet.

4. You can use this result set to obtain all data of the select statement {

// If SQL is select // 4. execute an SQL statement using an object // 5. obtain the ResultSet object // 6. traverse the result set to obtain the data ResultSet rs = st.exe cuteQuery ("select * from users "); // select SQL // int num = st.exe cuteUpdate ("update users set name = 'A'"); while (rs. next () {Object value1 = rs. getObject (1); String value2 = rs. getString (2); Int value3 = rs. getInt (3 );//... obtain the value of a column by column name or index of the column as a parameter}

ResultSet: used to represent the execution results of SQL statements. This object uses a table-like method to encapsulate the execution results. It maintains a cursor pointing to the table data row. At the beginning, the cursor calls the next () method before the first row, and the cursor points to the specific data row, then, use the get method to obtain the row of data.

Obtain any type of data

GetObject (int index );

GetObject (String columnName );

How to obtain the attribute of the corresponding column

}

5. release resources {

} finally{if(rs!=null){try {rs.close();} catch (Exception e) {rs = null;}}if(st!=null){try {st.close();} catch (Exception e) {st = null;}}if(conn!=null){try {conn.close();} catch (Exception e) {conn = null;}}}

1. After the jdbc program is running, you need to release the objects created during the program running to interact with the database, usually the ResultSet, Statement, and Connection objects.

2. Connection object. This is a rare resource. It must be released immediately after use. If the Connection cannot be closed in a timely and correct manner, the system will be easily down. So try to create it later and close it as early as possible.

3. To ensure code execution of the released program, it must be placed in the finally statement.

}

}

Create a tool class and simplify operations {

Public class JdbcUtils {// storage configuration information private static Properties config = new Properties (); // provides a static code block to ensure that the related information is loaded for the first time, load to the memory static {try {// obtain database information from the configuration file and load it to config. The file should be placed in the class path InputStream in = JdbcUtils. class. getClassLoader (). getResourceAsStream ("db. properties "); config. load (in); Class. forName (config. getProperty ("driver");} catch (Exception e) {// an error is reported directly. Due to a problem in the configuration file, the database throw new ExceptionInInitializerErro cannot be connected. R (e) ;}// provides a static method to connect the program. An exception is thrown directly to the calling program for public static Connection getConnection () throws SQLException {return DriverManager. getConnection (config. getProperty ("url"), config. getProperty ("user"), config. getProperty ("password");} // provides a method for releasing resources: public static void release (Connection conn, Statement st, ResultSet rs) {if (rs! = Null) {try {rs. close ();} catch (Exception e) {// record Exception information e. printStackTrace (); rs = null ;}} if (st! = Null) {try {st. close () ;}catch (Exception e) {e. printStackTrace (); st = null ;}} if (conn! = Null) {try {conn. close () ;}catch (Exception e) {e. printStackTrace (); conn = null ;}}}}

Db. properties file {

Driver = com. mysql. jdbc. Driver

Url = jdbc: mysql: // localhost: 3306/mydb

User = root

Password = root

}

Factory class: decouples operations on the program and dao layer ,{

// In the web program, the factory decouples the service and dao layers. When modifying the underlying code, you do not need to modify the upper-layer code. You only need to modify the configuration file to make public class DaoFactory {// read the configuration file to create the dao instance corresponding to the interface, you can also put the code in the constructor's private static Properties prop = new Properties (); static {try {// dao. the properties file contains: UserDao = cn. halm. dao. userDaoImplprop. load (DaoFactory. class. getClassLoader (). getResourceAsStream ("dao. properties ");} catch (Exception e) {throw new RuntimeException (e) ;}// the singleton mode needs to be designed. // 1. private DaoFactory () {}// 2. create a private instantiated object private static DaoFactory instance = new DaoFactory (); // 3. provide this object to the program public static DaoFactory getInstance () {return instance;} // provide a method to read information from the configuration file and create an implementation class instance public according to the interface
 
  
T createDao (Class interfaceClazz) {try {// obtain the interface name String interfaceName = interfaceClazz through the interface bytecode file. getSimpleName (); // obtain the name of the implementation class of this interface in the configuration file through the interface name String implName = prop. getProperty (interfaceName); // return the instance return (T) Class of the implementation Class. forName (implName ). newInstance () ;}catch (Exception e) {throw new RuntimeException (e );}}}
 

}

PreparedStatement object {

1. It is a subclass of Statement.

2. It can effectively prevent SQL injection:

SQL = "select * from users where username = '" + username + "' and password = '" + password + "'"; if two variables are password, username = "'or 1 = 1 or username ='", the statement is: select * from users where username = ''or true or username ='' --> select * from users where true; this statement can certainly find data from the database. This is SQL injection! If PreparedStatement is used, the passed variables are escaped to prevent this problem.

3. This object will pre-compile SQL statements to reduce the pressure on the database server and improve efficiency. The statement object will frequently compile SQL statements in the database, which may cause database Buffer Overflow.

4. allows you to replace parameters with placeholders to simplify the preparation of SQL statements.

Usage:

// Define an SQL statement that contains the Placeholder "?" String SQL = "select * from users where username =? And id =? "; // Pre-compiled SQL statement to obtain the object PreparedStatement st = conn1.prepareStatement (SQL); // starting from 1, different types of data use different methods st. setString (1, user. getUsername (); st. setInt (2, user.getid())st.exe cuteQuery (); // st.exe cuteUpdate (); insert, update, delete

Get the primary key value of the inserted data: getGeneratedKeys () Get the Automatically increasing value of the newly inserted data in the database, usually the primary key {

IfThis method is not available. If the primary key value is set to auto-increment in the database, the primary key value is not required for data insertion. Therefore, the primary key value cannot be directly obtained.

Note:In the mysql database, only one field can be modified as auto_increment.

// Obtain the object for executing the SQL Statement and specify a constant value Statement. RETURN_GENERATED_KEYS indicates that the Automatically increasing primary key value is returned. If it is not an insert SQL statement, this parameter value can be ignored. Without this parameter, the primary key value PreparedStatement st = conn is not returned. prepareStatement (SQL, Statement. RETURN_GENERATED_KEYS); // use this method to obtain the newly generated ResultSet object containing Automatically increasing values in the database. // This object contains only one column and stores the newly generated primary key value, it is not an insert SQL statement. This method is useless. ResultSet rs = st. getGeneratedKeys (); // traverses and obtains the rs value. next (); rs. getInt (1 );

}

}