jdbc is a set of specifications (a set of interfaces) specified by oracle company.
Driver: jdbc implementation class. Provided by database vendors.
We can operate different databases through a set of specifications (polymorphism)
jdbc role:
Connect database
Send sql statement
Processing result
2. jdbc operation steps:
1. database and table
2. create a project
3. import drive jar package
4. encoding:
Registration driven
Get connection
Writing sql
Create a precompiled statement executor
Setting parameters
Implementation of sql
Processing result
Release resources
// 1. registration drive
// Because the source code is also registered once, equivalent to two registrations, resulting in waste.
// So when the bytecode file of the class is loaded directly into memory by reflection, the static code block is automatically executed and registered successfully.
// DriverManager.registerDriver(new Driver());
//Bytecode file for driving class name
Class.forName("com.mysql.jdbc.Driver");
// 2. get connected
// public static Connection getConnection(String url,String user,String
// password)
// url: Address format of database: jdbc:mysql://Connect host IP: port number/database name
String url = "jdbc:mysql://localhost:3307/mydata";
java.sql.Connection c = DriverManager.getConnection(url, "root", "123");
// System.out.println(c);
// 3. Obtaining Executor Objects for SQL Statements by Connecting Objects
Statement stm = c.createStatement();
// 4. execute SQL statement
// int executeUpdate(String sql) can only be used for database add-delete operations
int row = stm.executeUpdate("UPDATE user3 SET uname='automobile',uprice=30000 WHERE uid=5;");
System.out.println(row);
// Release resources
c.close(); // 1. registration drive
Class.forName("com.mysql.jdbc.Driver");
// 2. get connected
String url = "jdbc:mysql://localhost:3307/mydata";
java.sql.Connection c = DriverManager.getConnection(url, "root", "123");
// 3. Get the executor object for the SQL statement
Statement st = c.createStatement();
// 4. Execute the SQL statement select statement
ResultSet rs = st.executeQuery("SELECT * FROM user3;");
// 5. processing result set
while(rs.next()){
System.out.println(rs.getInt("uid")+" "+rs.getString("uname")+" "
+rs.getDouble("uprice")+" "+rs.getString("udesc"));
}
// 6. release resources
rs.close();
st.close();
c.close();3. dbutils
Core classes or interfaces
QueryRunner: class name
Function: Operate sql statements
Constructor:
new QueryRunner(Datasource ds);
Be careful:
The bottom helps us create connections, create statement executors, and release resources.
Common methods:
query(..):
update(..):
DbUtils: Release resources, control transaction classes
closeQuietly(conn): Exceptions are handled internally
commitAndClose(Connection conn): Submit transactions and release connections
....
ResultSetHandler: Encapsulating Result Set Interface
ArrayHandler, ArrayListHandler, BeanHandler, BeanListHandler, ColumnListHandler, KeyedHandler, MapHandler, MapListHandler, ScalarHandler
(Understanding) Array Handler, encapsulates the first record of the query result into an array and returns it
(Understanding) ArrayListHandler, which encapsulates each record of the query result into an array and returns each array in a list
BeanHandler, which encapsulates the first record of the query result into a specified bean object and returns it
BeanListHandler encapsulates each record of the query result into a specified bean object and returns each bean object in a list.
ColumnListHandler, which returns a specified column of query results in a list
MapHandler, encapsulating the first record of the query result as a map, with the field name as key and the value returned
MapListHandler encapsulates each record of the query result into a map set and returns each map set into a list.
ScalarHandler, for aggregation functions such as count(*), returns a Long value
private static void beanListHander() throws Exception {
/*
* QueryRunner The fourth way to implement database query operation is to encapsulate the row data of the result set into javaBean objects
* Then encapsulate javaBean objects into List collections
*/
Connection conn = JDBCdemo6_1.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM user3;";
// BeanHandler<User3>(Class<User3> type)
List<User3> list = qr.query(conn, sql, new BeanListHandler<User3>(User3.class));
for (User3 user : list) {
System.out.println(user);
}
}
private static void beanHander() throws Exception {
/*
* QueryRunner The third way to implement database query operation is to encapsulate the row data of the result set into a JavaBean object, which is a class.
* The member variables of the class are the same as the fields in the table, and the class names are the same as the table names.
*/
Connection conn = JDBCdemo6_1.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM user3 WHERE uid=?;";
// BeanHandler<User3>(Class<User3> type)
User3 us = qr.query(conn, sql, new BeanHandler<User3>(User3.class), 2);
System.out.println(us);
}
private static void scalarHandler() throws Exception {
/*
* QueryRunner The sixth way to implement database query operation: for queries with only one result set
*/
Connection conn = JDBCdemo6_1.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "SELECT COUNT(*) FROM user3;";
Long row = qr.query(conn, sql, new ScalarHandler<Long>());
System.out.println(row);
}
public class DataSource2 {
private static BasicDataSource bds = new BasicDataSource();
static {
// Connection driver class database driver name must be configured
bds.setDriverClassName("com.mysql.jdbc.Driver");
// Database address
bds.setUrl("jdbc:mysql://localhost:3307/mydata");
bds.setUsername("root");
bds.setPassword("123");
//Connection object configuration in the object connection pool can be configured without default
bds.setInitialSize(10);//Number of Initialized Connections
bds.setMaxActive(8);//Maximum number of connections
bds.setMinIdle(2);//Maximum number of idle connections
bds.setMaxIdle(5);//Minimum number of idle connections
}
public static DataSource getBasicDataSource(){
return bds;
}
}
public class Test2 {
private static QueryRunner qr = new QueryRunner(DataSource2.getBasicDataSource());
public static void main(String[] args) {
// delete();
select();
}
private static void select() {
String sql = "SELECT * FROM user2;";
try {
List<Object[]> list = qr.query(sql, new ArrayListHandler());
for(Object[] obj:list){
for(Object ob:obj){
System.out.println(ob);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void delete() {
String sql = "DELETE FROM user2 WHERE num=?;";
try {
qr.update(sql, 6);
} catch (Exception e) {
e.printStackTrace();
}
}
}5. u3p0 connection pool automatically reclaims idle connections
public void method_3() throws Exception{
//ComboPooledDataSource cds=new ComboPooledDataSource();
// Use named configurations to perform default configurations for different databases if the names do not match
ComboPooledDataSource cds=new ComboPooledDataSource("itcast");
Connection conn=cds.getConnection();
String sql="insert into user1 values(?,?)";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setInt(1, 8);
pst.setString(2, "crocodile");
int row=pst.executeUpdate();
System.out.println(row);
}<c3p0-config> <!-- Default configuration, if not specified --> <default-config> <!-- Basic configuration --> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3307/day</property> <property name="user">root</property> <property name="password">123</property> <!--Extended configuration--> <property name="checkoutTimeout">30000</property> <property name="idleConnectionTestPeriod">30</property> <property name="initialPoolSize">10</property> <property name="maxIdleTime">30</property> <property name="maxPoolSize">100</property> <property name="minPoolSize">10</property> <property name="maxStatements">200</property> </default-config> <!-- Named configuration --> <named-config name="itcast"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3307/day</property> <property name="user">root</property> <property name="password">123</property> <!-- How many increases per time if the data connection in the pool is insufficient --> <property name="acquireIncrement">5</property> <property name="initialPoolSize">20</property> <property name="minPoolSize">10</property> <property name="maxPoolSize">40</property> <property name="maxStatements">20</property> <property name="maxStatementsPerConnection">5</property> </named-config> </c3p0-config>