How to use native JDBC?

JDBC
Java DataBase Connectivity, java database connection, is a Java API used to execute SQL statements.
JDBC is the standard specification for Java to access databases. It can provide unified access to different relational databases. It consists of a set of interfaces and classes written in Java language.

Driver
JDBC needs to connect to the driver. The driver is for two devices to communicate and meet a certain communication data format. The data format is specified by the equipment provider, and the equipment provider provides it for the device. Driver software, through which the device can communicate.

JDBC specification (mastering four core objects)
DriverManager: used to register the driver
Connection: represents the connection created with the database
Statement: operates the database sql statement Object
ResultSet: result set or a virtual table

Use JDBC technology, through the driver provided by mysql, to operate the database implementation steps:
1. Register the driver
Inform the JVM that we use What is the driver (mysql, oracle....)
DriverManager.registerDriver(new com.mysql.jdbc.Driver()); It is not recommended to use
There are 2 reasons:
>Cause The driver was registered twice.
>Driver jar that strongly relies on the database
Solution:
Class.forName("com.mysql.jdbc.Driver");
2. Get the connection to the database
The database is TCP Program server, connection server (through 3-way handshake)
is equivalent to establishing a connection path from the java program to the database server
static Connection getConnection(String url, String user, String password)
Try to establish A connection to the given database URL.
Parameter description: url The location where the database needs to be connected (web address) user user name password password
For example: getConnection("jdbc:mysql://localhost:3306/day06", "root", "root");
URL: An agreement between SUN and the database manufacturer.
jdbc:mysql://localhost:3306/day06
Protocol sub-protocol IP: port number database
mysql: jdbc:mysql://localhost:3306/day04 or jdbc:mysql:///day14 (Default local connection)
oracle database: jdbc:oracle:thin:@localhost:1521:sid
3. Get the executor object
The object that executes the SQL statement, its function is to execute the SQL
interface The implementation is in the database driver. All interactions with the database are based on connection objects.
Statement createStatement(); //Create an object for operating sql statements
4. Execute the SQL statement and obtain the result set
Use the executor object to execute the SQL statement
Obtain the result set of the SQL statement (add, delete, modify) : Integer, number of valid rows to execute Query: What is returned is a result set)
Commonly used methods:
? int executeUpdate(String sql); --Execute insert update delete statement.
? ResultSet executeQuery(String sql ); --Execute the select statement.
? boolean execute(String sql); --Return true only when the select is executed and there is a result, and false is returned when executing other statements.
5. Process the result set
ResultSet is actually a two-dimensional table. We can call its boolean next() method to point to a certain row of records. When the next() method is called for the first time, it points to the location of the first row of records. At this time, ResultSet can be used Provided getXXX(int col) method (different from index starting from 0, column starting from 1) to get the data of the specified column:
rs.next();//Point to the first row
rs.getInt (1);//Get the data of the first row and the first column
Common methods:
? Object getObject(int index) / Object getObject(String name) Get any object
? String getString(int index )/ String getString(String name) Get the string
? int getInt(int index)/int getInt(String name) Get the integer
? double getDouble(int index)/ double getDouble(String name) Get the double precision Floating point type
6. Release resources
Like IO streams, everything needs to be closed after use! The order of closing is to get it first and then close it, and to get it later and close it first.
Using JDBC to add, delete, modify and query the database code demonstration:

  1 public static void main(String[] args) throws Exception {  2         //1.注册驱动  3         Class.forName("com.mysql.jdbc.Driver");  4         //2.获取数据库连接  5         String url = "jdbc:mysql://localhost:3306/mybase4";  6         String user = "root";  7         String password = "root"; 
  8         Connection conn = DriverManager.getConnection(url, user, password);  9         //3.获取执行者对象 10         Statement stat = conn.createStatement(); 11         //调用更新数据的方法 12         //update(stat); 13         //调用删除数据的方法 14         //delete(stat); 15         //调用增加数据的方法 16         //insert(stat); 17         //调用查询数据的方法 18         select(stat); 19         //6.释放资源 20         stat.close(); 21         conn.close(); 22     } 23  24     /* 25      * 使用JDBC技术,查询数据库中表的数据 26      */ 27     private static void select(Statement stat) throws Exception { 28         //拼接sql语句 29         String sql = "SELECT * FROM category"; 30         /* 31          * 4.执行sql语句 32          * 使用Statement中的方法 33          * ResultSet executeQuery(String sql) 执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。 
 34          * 返回值ResultSet标准接口的实现类对象,实现类对象由mysql驱动提供,可以使用ResultSet接口接收 35          */ 36         ResultSet rs = stat.executeQuery(sql); 37         System.out.println(rs);//com.mysql.jdbc.JDBC4ResultSet@1acb189 38         /* 39          * 5.处理结果 40          * ResultSet中有一个方法 41          * boolean next() 将光标从当前位置向前移一行。 42          * 如果新的当前行有效，则返回 true；如果不存在下一行，则返回 false 
 43          * 如果有结果集返回true,若果没有结果集返回false 44          * 相当于迭代器中的hasNext方法 45          */ 46         while(rs.next()){ 47             /* 48              * next返回true,有结果集 49              * 取出结果集 50              * 使用ResultSet中的方法getXXX(参数); 51              * 参数: 52              *     int columnIndex:列所在的索引,从1开始 53              *     String columnLabel:列名 54              * 注意: 55              *     如果使用getInt,getDouble指定数据类型的方法,返回值就是对应的数据类型 56              *     如果使用getObject方法返回值是object类型(只是打印可用) 57              * 如果使用getString方法返回值是String类型 58              */ 59             /*int i1 = rs.getInt(1); 60             String s2 = rs.getString(2); 61             System.out.println(i1+"\t"+s2);*/ 62              63             //System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)); 64             System.out.println(rs.getObject("cid")+"\t"+rs.getObject("cname")); 65             //5.释放资源 66             rs.close(); 67         } 68     } 69  70     /* 71      * 使用JDBC技术,对数据库中的表数据进行增加 72      */ 73     private static void insert(Statement stat) throws SQLException { 74         //拼接sql语句 75         String sql = "INSERT INTO category(cname) VALUES('玩具')"; 76         //4.执行sql语句 77         int row = stat.executeUpdate(sql); 78         //5.处理结果 79         if(row>0){ 80             System.out.println("增加数据成功!"); 81         }else{ 82             System.out.println("增加数据失败!"); 83         } 84          85     } 86  87     /* 88      * 使用JDBC技术,对数据库中的表数据进行删除 89      */ 90     private static void delete(Statement stat) throws Exception { 91         //拼接sql语句 92         String sql = "DELETE FROM category WHERE cid=5"; 93         //4.执行sql语句 94         int row = stat.executeUpdate(sql); 95         //5.处理结果 96         if(row>0){ 97             System.out.println("删除数据成功!"); 98         }else{ 99             System.out.println("删除数据失败!");100         }101     }102 103     /*104      * 使用JDBC技术,对数据库中的表数据进行更新105      */106     private static void update(Statement stat) throws Exception {107         //拼接sql语句108         String sql = "UPDATE category SET cname='鞋帽' WHERE cid=6";109         //4.执行sql语句110         int row = stat.executeUpdate(sql);111         //5.处理结果112         if(row>0){113             System.out.println("更新数据成功!");114         }else{115             System.out.println("更新数据失败!");116         }117     }

 1 JDBC工具类 2 “获得数据库连接”操作，将在以后的增删改查所有功能中都存在，可以封装工具类JDBCUtils。提供获取连接对象的方法，从而达到代码的重复利用。 3 代码演示: 4 public class JDBCUtils { 5      6     //私有构造方法,防止外界创建对象调用方法 7     private JDBCUtils() { 8     } 9     10     //定义Connectionn变量11     private static Connection conn;12     13     //保证代码只执行一次,可以放入静态代码块中14     static{15         try {16             //注册驱动17             Class.forName("com.mysql.jdbc.Driver");18             //获取连接19             String url="jdbc:mysql://127.0.0.1:3306/mybase4";20             String user="root";21             String password = "root";22             conn = DriverManager.getConnection(url, user, password);23         } catch (Exception e) {24             //注意,如果连接数据库失败,停止程序25             throw new RuntimeException(e+"连接数据库失败");26         }27     }28     29     //创建一个静态方法,获取数据库连接对象,并返回30     public static Connection getConnection(){31         return conn;32     }33     34     /*35      * 创建一个静态方法,对资源进行释放36      * ResultSet rs37      * Statement stat38      * Connection conn39      */40     public static void close(ResultSet rs,Statement stat,Connection conn){41         try {42             if(rs!=null){43                 rs.close();44             }45         } catch (SQLException e) {46             e.printStackTrace();47         }48         try {49             if(stat!=null){50                 stat.close();51             }52         } catch (SQLException e) {53             e.printStackTrace();54         }55         try {56             if(conn!=null){57                 conn.close();58             }59         } catch (SQLException e) {60             e.printStackTrace();61         }62     }63 }

sql injection problem
SQL injection: The content entered by the user is used as part of the SQL statement syntax, changing the true meaning of the original SQL.
Assume there is a login case and the SQL statement is as follows:
SELECT * FROM user table WHERE NAME = User name entered by the user AND PASSWORD = Password entered by the user;
At this time, when the user enters the correct account number and password , and the user is allowed to log in after the information is queried. But when the user enters the account number XXX and the password: XXX' OR 'a'='a, the actually executed code becomes:
SELECT * FROM user table WHERE NAME = 'XXX' AND PASSWORD =' OR 'a'='a';
At this time, the above query statement can always produce results. Then the user logs in successfully directly. Obviously we don't want to see such a result. This is a SQL injection problem.
To this end, we use PreparedStatement to solve the corresponding problem.

preparedStatement: Precompiled object, which is a subclass of Statement object.
Features:
High performance
Will compile the sql statement first
Can filter out the keywords entered by the user.

PreparedStatement preprocessing object, all actual parameters in each SQL statement processed must be replaced with placeholders?.
String sql = "select * from user where username = ? and password = ?";
To use PreparedStatement, you need to complete the following 3 steps:
1.PreparedStatement preprocessing object code:
Obtain pre-processing To process the object, you need to provide the SQL statement that has been processed using placeholders
PreparedStatement psmt = conn.prepareStatement(sql)
2. Set the actual parameters
void setXxx(int ​​index, Xxx xx) will specify the parameters Set the value of the specified type
Parameter 1: index Actual parameter sequence number, starting from 1.
Parameter 2: xxx actual parameter value, xxx represents the specific type.
For example:
setString(2, "1234") Replace the placeholder at the second position in the SQL statement with the actual parameter "1234"
3. Execute the SQL statement:
int executeUpdate (); --Execute insert update delete statement.
ResultSet executeQuery(); --Execute select statement.
boolean execute(); --Execute select and return true. Execute other statements and return false.

The above is the detailed content of How to use native JDBC?. For more information, please follow other related articles on the PHP Chinese website!