JDBC details
brief introduction
JDBC refers to Java database connection. It is a standard Java application programming interface (JAVA API) used to connect Java programming language and a wide range of databases. Fundamentally, JDBC is a specification. It provides a complete set of interfaces that allow portable access to the underlying database. Therefore, different types of executable files can be written in Java.
Execution process:
Connect to a data source, such as a database.
Pass query and update instructions to the database.
Process the database response and return the results.
Required interfaces and classes
1.Connection interface
The database of Connection object can provide information to describe its table, supported SQL syntax, stored procedures and the functions of this Connection. By default, the Connection object is in auto commit mode, which means that it automatically commits changes after each statement is executed. If the auto commit mode is disabled, the commit method must be explicitly called in order to commit changes; Otherwise, database changes cannot be saved.
createStatement() method:
Create a Statement object to send SQL statements to the database. SQL statements without parameters are usually executed using a Statement object. If the same SQL Statement is executed multiple times, it may be more effective to use the PreparedStatement object.
getMetaData() method:
Gets the DatabaseMetaData object that contains metadata about the database to which the Connection object is connected. Metadata includes information about database tables, supported SQL syntax, stored procedures, and the capabilities of this Connection.
Rollback (savepoint) method:
Cancels all changes made in the current transaction and releases all database locks currently saved by this Connection object. This method should only be used when auto submit mode is disabled.
setAutoCommit(boolean autoCommit) method:
Set the auto submit mode for this connection to the given state. If the connection is in auto commit mode, all its SQL statements are executed and committed as separate transactions. Otherwise, its SQL statements will be grouped into transactions terminated by calling the commit method or rollback method. By default, new connections are in auto submit mode.
Savepoint setSavepoint(String name) method:
Add a transaction rollback point, create a Savepoint with the given name in the current transaction, and return a new Savepoint object representing it.
2. Statement interface
An object used to execute a static SQL statement and return the results it generates.
By default, only one ResultSet object can be opened for each Statement object at a time. Therefore, if reading one ResultSet object intersects reading the other, the two objects must be generated by different Statement objects. If there is an open current ResultSet object for a Statement, all execution methods in the Statement interface implicitly close it.
executeUpdate(String sql) method:
Execute a given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement that does not return anything (such as a SQL DDL statement).
executeQuery(String sql) method:
Executes the given SQL statement that returns a single ResultSet object.
3.ResultSet interface
The data table representing the result set of the database is usually generated by executing the statement of querying the database.
The ResultSet object has a pointer to its current data row. Initially, the pointer is placed before the first line. The next method moves the pointer to the next line; Because this method returns false when there is no next row in the ResultSet object, it can be used in the while loop to iterate over the result set.
The default ResultSet object is not updatable and has only one pointer moving forward. Therefore, it can only be iterated once and only in the order from the first row to the last row. You can generate scrollable and / or updatable ResultSet objects. The following code snippet, where con is a valid Connection object, demonstrates how to generate an updatable result set that is scrollable and unaffected by other updates. See the ResultSet field for additional options.
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others,
// and will be updatable
next() method:
Judge the acceptance result of resultset, and use circular traversal to query the result! When the parameter types in the database table are known, the getint method of resultset is used.
Properties class (the parent class is hashtable)
The properties class represents a persistent set of properties. Properties can be saved in or loaded from a stream. Each key and its corresponding value in the attribute list is a string.
An attribute list can contain another attribute list as its "default value"; If the attribute key cannot be searched in the original attribute list, the second attribute list is searched.
Because Properties inherit from Hashtable, the put and putAll methods can be applied to the Properties object. But these two methods are strongly opposed because they allow the caller to insert items whose key or value is not Strings. Instead, use the setProperty method. If the store or save method is called on a "dangerous" Properties object (that is, a key or value that contains a non String), the call will fail.
The load and store methods load and store attributes in the simple row oriented format specified below. This format uses ISO 8859-1 character encoding. Unicode escape characters can be used to write characters that cannot be directly represented in this encoding; Only a single 'u' character is allowed in an escape sequence. You can use the native2ascii tool to convert property files and other character encodings to each other.
getProperty(String key) method:
Search for attributes in this attribute list with the specified key.
setProperty(String key, String value) method:
Call the put method of Hashtable.
DataSource class
The factory is used to provide connections to the physical data source represented by this DataSource object. As an alternative to the DriverManager facility, the DataSource object is the preferred method for obtaining connections. Objects that implement the DataSource interface are usually registered in a naming service based on the JavaTM Naming and Directory Interface (JNDI) API.
The DataSource interface is implemented by the driver vendor. There are three types of implementations:
1. Basic implementation - generate standard Connection object
2. Connection pool implementation - generate connection objects that automatically participate in the connection pool. This implementation works with the middle tier connection pool manager.
3. Distributed transaction implementation - generates a Connection object that can be used for distributed transactions and almost always participates in the Connection pool. This implementation works with the middle tier transaction manager and almost always with the Connection pool manager.
The properties of the DataSource object can be modified as needed. For example, if you move a data source to another server, you can change server related properties. The advantage is that because the properties of the data source can be changed, no code accessing the data source needs to be changed.
Drivers accessed through DataSource objects are not registered with the DriverManager. Retrieve the DataSource object through a lookup operation, and then use it to create a Connection object. Using the basic implementation, the Connection obtained through the DataSource object is the same as the Connection obtained through the DriverManager facility.
getConnection() method:
An attempt was made to establish a connection to the data source represented by this DataSource object.
DriverManager class
Basic services that manage a set of JDBC drivers.
Note: DataSource interface is a new content in JDBC 2.0 API. It provides another method to connect to data source. Using a DataSource object is the preferred method to connect to a data source.
getConnection(String url) method:
An attempt was made to establish a connection to the given database URL.
Basic JDBC
As the realization of simple functions, it is not a general method. Each time, the function is single and the function is not unified.
package jdbc;
import java.sql.*;
public class Connect {
public static void main(String [] args) throws Exception{
//Class.forName("com.mysql.cj.jdbc.Driver");
//1. Register jdbc Driver
String driverName="com.mysql.cj.jdbc.Driver";
//2. Get the connection object
String dbURL="jdbc:mysql://127.0.0.1:3306/mytest?useSSL=false&serverTimezone=UTC";
String userName="root";
String userPwd="123456";//Database password (self set)
try{
Class.forName(driverName);
System.out.println("Loading driver succeeded!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Failed to load driver!");
}
try{
Connection dbConn=DriverManager.getConnection(dbURL,userName,userPwd);
System.out.println("Database connection succeeded!");
}catch(Exception e){
e.printStackTrace();
System.out.print("MySQL80 Connection failed!");
}
Connection Conn=DriverManager.getConnection(dbURL,userName,userPwd);
Statement stat=Conn.createStatement();
String sql = "select * from tb1";
ResultSet result = stat.executeQuery(sql);
while(result.next()) {
System.out.print("username:"+result.getString("username")+"|");
System.out.println("age:"+result.getInt("age"));
}
result.close();
stat.close();
Conn.close();
}
}
Universal version of JDBC (using Statement)
1.JDBCUtils general abstract class:
package mysql.jdbc;
import fs.reflex.ReflectionUtils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public abstract class JDBCUtils {
private static String url = null;
private static String name =null;
private static String password = null;
private Connection conn= null;
private Statement st = null;
private PreparedStatement ps =null;
private ResultSet rs = null;
private ResultSetMetaData rsm =null;
private static DataSource dataSource =null;
static{
Properties properties = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(is);
String driver =properties.getProperty("driverClassName"); url = properties.getProperty("url");
name = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getconnection() throws SQLException {//Get link
return DriverManager.getConnection(url,name,password);
}
//---------------statement method
public boolean update(String sql) {//Database modification
int bRet =0;
try {
conn = getconnection();
st = conn.createStatement();
bRet = st.executeUpdate(sql);
//System.out.println(bRet);
} catch (SQLException e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
return bRet!=0;
}
public static void jdbcClose(Connection conn,Statement st,ResultSet rs) {//jbdc database shutdown
try {
rs.close();
if(rs!=null){
rs = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
st.close();
if(st!=null){
st = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
if(conn!=null){
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static void jdbcClose(Connection conn,Statement st) {
try {
st.close();
if(st!=null){
st = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
if(conn!=null){
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public boolean delete(String sql) {//delete
int bRet =0;
try {conn = getconnection();
st = conn.createStatement();
bRet = st.executeUpdate(sql);
//System.out.println(bRet);
} catch (SQLException e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
return bRet!=0;
}
public boolean inster(String sql) throws SQLException {
int i = 0;
try {
conn = getconnection();
st = conn.createStatement();
i = st.executeUpdate(sql);
} catch (SQLException e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
return i!=0;
}
//Single or multiple
public abstract Object reToObj(ResultSet rs);
public abstract List<Object> rsToList(ResultSet rs);
public Object queryOne(String sql) throws SQLException {
Object obj =null;
conn = getconnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
obj = reToObj(rs);
return obj;
}
public List<Object> queryAll(String sql) throws SQLException {
List<Object> list =null;
conn = getconnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
list = rsToList(rs);
return list;
}
}
2.db.properties file
It is convenient for unified planning and management of drivers, users, passwords and database tables.
driverClassName = com.mysql.cj.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/test1 username = root password = 123456