JDBC

What is JDBC?

1. It is a common interface (API) for SQL database access and operation independent of a specific database management system.

   • Why provide a fixed interface?

     • A: it is easy to operate and improve efficiency. Different databases can be programmed through this set of interfaces. After layering, programmers only need to program through the interface - interface programming.
       

2. Other databases need to use this interface to write their own database method, namely driver. Third party libraries and jar packages provided by manufacturers are needed here

   • How to download it? Good question
     • A: please Baidu Download the corresponding interface

3. JDBC provides a unified way to access different databases and shields some details for developers.

4. The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.

JDBC architecture

What is the JDBC architecture?

• Application oriented API: used by programmers.
• Database oriented API: used by drivers written by developers.

How to write JDBC program

First import the jar package, that is, the java driver written by the database manufacturer

Connection drive

mode

// Take connecting to mysql server as an example
  public  void  testConnect1() throws SQLException {
      //Created a mysql driver class
        Driver driver = new com.mysql.cj.jdbc.Driver();
        /*
            jdbc:mysql agreement
            ip : port
           */
        String url = "jdbc:mysql://localhost:3306/test";
        Properties info=new Properties();
      	// The configuration file is used here
        info.setProperty("user","root");
        info.setProperty("password", "123456");
        driver.connect(url,info);
    }
/*
		The disadvantages of this way of writing are:
		The content is dead, and the source code needs to be modified when updating. So I don't usually write that
*/

public void  getConnect5() throws Exception {
    //1. Read configuration
  InputStream is= ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
  Properties pros = new Properties();
  pros.load(is);
  String user = pros.getProperty("user");
  String password= pros.getProperty("password");
  String url = pros.getProperty("url");
  String DriverClass = pros.getProperty("driverClass");
  //Load driver
  Class.forName(DriverClass);

}
//The configuration file name is JDBC properties
/*
user=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver

 The one here is better. If you change the database, you don't need to modify the source code. The content can be modified by modifying the configuration file
*/

Create Connection object

//Establish a connection to the given database URL.
Conection conn= DriverManager.getConnection(url, user, password);
// Connect database

Create Statement object

Statement

1. An object used to execute a static SQL statement and return the results it generates.

public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("user name:");
String userName = scan.nextLine();
System.out.print("password:");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD =
'='1' or '1' = '1';
    String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName
+ "' AND PASSWORD = '" + password
+ "'";
User user = get(sql, User.class);
if (user != null) {
System.out.println("Login successful!");
} else {
System.out.println("Wrong user name or password!");
}
}

Disadvantages:

1. There is a risk of being injected by sql
2. It needs to be spliced by lines
3. Blob data cannot be transferred because it is a static statement

PrepatedStatement

1. The SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.

advantage

1. Greatly reduce the risk of sql injection
2. Pass? (question mark) implementation, space occupation

/*
	This code will not work
*/
Connection conn = null;
PreparedStatement ps = null;
//1. Get the connection to the database
conn = JDBCUtils.getConnection();
//2. Get the instance of PreparedStatement (or: precompiled sql statement)
// Through here? To occupy a seat
String sql = "select * from test where id =?";
ps = conn.prepareStatement(sql);
// sql start position is 1, id =10
ps.setInt(1, 10);
// It can be used in any type
ps. setoject(1, 10);

Execute SQL statement

// Execute executeQuery() to get the result set: ResultSet
ResultSet rs = ps.executeQuery();	
// If the implementation is the addition, deletion and modification This command can be executed
ps.execute()

ResultSet

1. The query needs to call the executeQuery() method of PreparedStatement, and the query result is a ResultSet object
2. The ResultSet object encapsulates the result set of database operation in the form of logical table, and the ResultSet interface is provided and implemented by the database manufacturer
3. What ResultSet returns is actually a data table. There is a pointer to the front of the first record in the data table.
4. The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object. Call the next () method to check whether the next line is valid. If valid, the method returns true and the pointer moves down.
5. It is equivalent to the combination of hasNext() and next() methods of Iterator object. When the pointer points to a row, you can get the value of each column by calling getXxx(int index) or getXxx(int columnName).

Close connection

Close first ResultSet -> PreparedStatement->Connection
rs.close();
ps.close();
//Connection object
con.close();