Article directory
- Apache-DBUtils implements CRUD operations
-
- 1 Introduction to Apache-DBUtils
- 2 Usage of main API
-
- 2.1 DbUtils
- 2.2 QueryRunner class
- 2.3 ResultSetHandler interface and implementation class
- 3 JDBCUtil tool class writing
-
- 3.1 Guide package
- 3.2 Writing configuration files
- 3.3 Writing code
- 4BaseDao written
Apache-DBUtils implements CRUD operations
1 Introduction to Apache-DBUtils
-
commons-dbutils is an open source JDBC tool library provided by the Apache organization. It is a simple encapsulation of JDBC with extremely low learning costs. Using dbutils can greatly simplify the workload of jdbc coding without affecting the performance of the program.
-
API introduction:
- org.apache.commons.dbutils.QueryRunner
- org.apache.commons.dbutils.ResultSetHandler
- Tool class: org.apache.commons.dbutils.DbUtils
-
API package description:
-
2 Use of main API
2.1 DbUtils
- DbUtils: Provides tool classes for routine tasks such as closing connections and loading JDBC drivers. All methods in them are static. The main methods are as follows:
- public static void close(…) throws java.sql.SQLException: The DbUtils class provides three overloaded close methods. These methods check whether the supplied parameter is NULL, and if not, they close the Connection, Statement, and ResultSet.
- public static void closeQuietly(…): This type of method can not only avoid closing when Connection, Statement and ResultSet are NULL, but also hide some SQLEeceptions thrown in the program.
- public static void commitAndClose(Connection conn)throws SQLException: Used to commit the connection transaction and then close the connection
- public static void commitAndCloseQuietly(Connection conn): Used to submit the connection and then close the connection without throwing a SQL exception when closing the connection.
- public static void rollback(Connection conn)throws SQLException: conn is allowed to be null because the method makes a judgment internally
- public static void rollbackAndClose(Connection conn)throws SQLException
- rollbackAndCloseQuietly(Connection)
- public static boolean loadDriver(java.lang.String driverClassName): This side loads and registers the JDBC driver and returns true if successful. Using this method, you do not need to catch this exception ClassNotFoundException.
2.2 QueryRunner class
-
This class simplifies SQL queries. It can be used in combination with ResultSetHandler to complete most database operations and greatly reduce the amount of coding.
-
The QueryRunner class provides two constructors:
- default constructor
- A constructor that requires a javax.sql.DataSource as a parameter
-
Main methods of QueryRunner class:
- Update
- public int update(Connection conn, String sql, Object… params) throws SQLException: Used to perform an update (insert, update or delete) operation.
- …
- Insert
- public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throws SQLException: Only INSERT statements are supported, where rsh – The handler used to create the result object from the ResultSet of auto-generated keys. Return value: An object generated by the handler. That is, the automatically generated key value
- …
- Batch processing
- public int[] batch(Connection conn,String sql,Object[][] params) throws SQLException: INSERT, UPDATE, or DELETE statement
- public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException: Only supports INSERT statements
- …
- Query
- public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException: Execute a query operation, in which the value of each element in the object array is used as the replacement parameter of the query statement. This method handles the creation and closing of PreparedStatement and ResultSet itself.
- Update
2.3 ResultSetHandler interface and implementation class
-
This interface is used to process java.sql.ResultSet and convert the data into another form as required.
-
The ResultSetHandler interface provides a single method: Object handle (java.sql.ResultSet .rs).
-
The main implementation classes of the interface:
- ArrayHandler: Convert the first row of data in the result set into an object array.
- ArrayListHandler: Convert each row of data in the result set into an array, and then store it in the List.
- **BeanHandler:** Encapsulate the first row of data in the result set into a corresponding JavaBean instance.
- **BeanListHandler:** Encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
- ColumnListHandler: Stores the data of a certain column in the result set into a List.
- KeyedHandler(name): Encapsulate each row of data in the result set into a Map, and then save these maps into a map whose key is the specified key.
- **MapHandler:** Encapsulate the first row of data in the result set into a Map. The key is the column name and the value is the corresponding value.
- **MapListHandler:**Encapsulate each row of data in the result set into a Map, and then store it in the List
- **ScalarHandler:** Query a single value object
3 JDBCUtil tool class writing
3.1 Guide package
3.2 Writing configuration files
Create the druid.properties file, note that the file name cannot be changed.
username=root password=root url=jdbc:mysql://localhost:3306/jdbc_learn?useUnicode=true &characterEncoding=utf8 &rewriteBatchedStatements=true driverClassName=com.mysql.cj.jdbc.Driver initialSize=10 maxActive=10
3.3 Writing code
Create JDBCUtil.java file
/**
* ClassName: JDBCUtil
* Description: Tool class for operating database
* Druid database obtains the database connection
* DbUtils closes database resources
*
* @Create 2023/10/29 14:35
* @Version 1.0
*/
public class JDBCUtil {<!-- -->
/**
* Use the configuration file of the Druid database connection pool to obtain the database connection: recommended
*
* @return
* @throwsException
*/
//Create a Druid database connection pool
private static DataSource source = null;
static {<!-- -->
try {<!-- -->
Properties pros = new Properties();
//Read configuration file
//InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
// The web project reads the configuration file and uses it
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws Exception {<!-- -->
Connection conn = source.getConnection();
return conn;
}
/**
* Close resource operations, used when adding, deleting or modifying
* Use DbUtils to close resources
* DbUtils.closeQuietly();
* or
* DbUtils.close();
*
* @param conn
* @param ps
*/
public static void closeResource(Connection conn, Statement ps) {<!-- -->
try {<!-- -->
DbUtils.close(conn);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
try {<!-- -->
DbUtils.close(ps);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
}
/**
* The operation of closing resources, used when querying
* Use DbUtils to close resources
* DbUtils.closeQuietly();
* or
* DbUtils.close();
*
* @param conn
* @param ps
* @param rs
*/
public static void closeResource(Connection conn, Statement ps, ResultSet rs) {<!-- -->
// 7 Resource close
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}
}
4 Written by BaseDao
/**
* ClassName: BaseDao
* Description:
* Use DbUtils tool class to implement BaseDao
* @Create 2023/11/3 13:58
* @Version 1.0
*/
public abstract class BaseDao<T> {<!-- -->
private QueryRunner queryRunner = new QueryRunner();
//Define a variable to receive the generic type
private Class<T> type;
// Get the Class object of T and get the type of the generic. The generic is determined when it is inherited by a subclass.
public BaseDao() {<!-- -->
// Get the subclass type
Class clazz = this.getClass();
// Get the type of parent class
// getGenericSuperclass() is used to get the type of the parent class of the current class
// ParameterizedType represents a type with generics
ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
// Get the specific generic type getActualTypeArguments Get the specific generic type
// This method will return an array of Type
Type[] types = parameterizedType.getActualTypeArguments();
// Get the specific generic type
this.type = (Class<T>) types[0];
}
/**
* General addition, deletion and modification operations
*
* @param sql
* @param params
* @return
*/
public int update(Connection conn, String sql, Object... params) {<!-- -->
int count = 0;
try {<!-- -->
count = queryRunner.update(conn, sql, params);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
return count;
}
/**
* Get an object
*
* @param sql
* @param params
* @return
*/
public T getBean(Connection conn, String sql, Object... params) {<!-- -->
T t = null;
try {<!-- -->
t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
return t;
}
/**
* Get all objects
*
* @param sql
* @param params
* @return
*/
public List<T> getBeanList(Connection conn, String sql, Object... params) {<!-- -->
List<T> tList = null;
try {<!-- -->
tList = queryRunner.query(conn, sql, new BeanListHandler<>(type), params);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
return tList;
}
/**
* Get special values, specially used to execute sql statements like select count(*)...
*
* @param sql
* @param params
* @return
*/
public Object getValue(Connection conn, String sql, Object... params) {<!-- -->
Object count = null;
try {<!-- -->
count = queryRunner.query(conn, sql, new ScalarHandler(), params);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
return count;
}
}
For web projects, the druid database connection pool is required. Configure the following content in web.xml
<!-- ============Alibaba database connection pool start============ -->
<filter>
<filter-name>druidWebStatFilter</filter-name>
<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
<init-param>
<param-name>exclusions</param-name>
<param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>druidWebStatFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<servlet>
<servlet-name>druidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>druidStatView</servlet-name>
<url-pattern>/druid/*</url-pattern>
</servlet-mapping>
<!-- ============Alibaba database connection poolend============ -->
The learning content of the attached article comes from Shang Silicon Valley.