jdbc 工具类,具体见下面代码,直接可以用。
public class JDBCUtils {
private static final String URL = "jdbc:mysql://127.0.0.1:3306/mybatis" ;
private static final String USER = "root" ;
private static final String PASSWORD = "root" ;
private static final String DRIVER = "com.mysql.cj.jdbc.Driver" ;
private static final int BATCH_SIZE = 2 ;
private static final String INSERT_SQL = "INSERT INTO user (name, age) VALUES (?, ?)" ;
private static final String UPDATE_SQL = "UPDATE user SET name=?, age=? WHERE id=?" ;
private static final String DELETE_SQL = "DELETE FROM user WHERE id=?" ;
public static final String SELECT_SQL = "SELECT * FROM user WHERE id=?" ;
public static Connection getConnection ( ) throws Exception {
Class . forName ( DRIVER ) ;
return DriverManager . getConnection ( URL , USER , PASSWORD ) ;
}
public static void closeConnection ( Connection connection) {
try {
if ( connection != null ) {
connection. close ( ) ;
}
} catch ( SQLException e) {
throw new RuntimeException ( e) ;
}
}
public static void closeResultSet ( ResultSet resultSet) {
try {
if ( resultSet != null ) {
resultSet. close ( ) ;
}
} catch ( SQLException e) {
throw new RuntimeException ( e) ;
}
}
public static void closeStatement ( PreparedStatement preparedStatement) {
try {
if ( preparedStatement != null ) {
preparedStatement. close ( ) ;
}
} catch ( SQLException e) {
throw new RuntimeException ( e) ;
}
}
public static void insert ( Map < String , Object > paramMap) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( INSERT_SQL ) ;
preparedStatement. setString ( 1 , ( String ) paramMap. get ( "name" ) ) ;
preparedStatement. setInt ( 2 , paramMap. get ( "age" ) == null ? 0 : ( int ) paramMap. get ( "age" ) ) ;
preparedStatement. executeUpdate ( ) ;
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
}
}
public static void batchInsert ( List < Map < String , Object > > paramMapList, int size) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
long start = System . currentTimeMillis ( ) ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( INSERT_SQL ) ;
connection. setAutoCommit ( false ) ;
for ( int i = 1 ; i <= size; i++ ) {
Map < String , Object > paramMap = paramMapList. get ( i- 1 ) ;
preparedStatement. setString ( 1 , ( String ) paramMap. get ( "name" ) ) ;
preparedStatement. setInt ( 2 , paramMap. get ( "age" ) == null ? 0 : ( int ) paramMap. get ( "age" ) ) ;
preparedStatement. addBatch ( ) ;
if ( i % BATCH_SIZE == 0 ) {
preparedStatement. executeBatch ( ) ;
connection. commit ( ) ;
preparedStatement. clearBatch ( ) ;
}
}
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
long end = System . currentTimeMillis ( ) - start;
System . out. println ( end) ;
}
}
public static void delete ( Map < String , Object > paramMap) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( DELETE_SQL ) ;
preparedStatement. setInt ( 1 , ( int ) paramMap. get ( "id" ) ) ;
preparedStatement. executeUpdate ( ) ;
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
}
}
public static void update ( Map < String , Object > paramMap) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( UPDATE_SQL ) ;
preparedStatement. setString ( 1 , ( String ) paramMap. get ( "name" ) ) ;
preparedStatement. setInt ( 2 , paramMap. get ( "age" ) == null ? 0 : ( int ) paramMap. get ( "age" ) ) ;
preparedStatement. setInt ( 3 , ( int ) paramMap. get ( "id" ) ) ;
preparedStatement. executeUpdate ( ) ;
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
}
}
public static void selectById ( Map < String , Object > paramMap) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
ResultSet resultSet = null ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( SELECT_SQL ) ;
preparedStatement. setInt ( 1 , ( int ) paramMap. get ( "id" ) ) ;
resultSet = preparedStatement. executeQuery ( ) ;
while ( resultSet. next ( ) ) {
int id = resultSet. getInt ( "id" ) ;
String name = resultSet. getString ( "name" ) ;
int age = resultSet. getInt ( "age" ) ;
System . out. println ( id + " " + name + " " + age) ;
}
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeResultSet ( resultSet) ;
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
}
}
public static void selectAll ( ) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
ResultSet resultSet = null ;
try {
connection = JDBCUtils . getConnection ( ) ;
preparedStatement = connection. prepareStatement ( "SELECT * FROM user " ) ;
resultSet = preparedStatement. executeQuery ( ) ;
while ( resultSet. next ( ) ) {
int id = resultSet. getInt ( "id" ) ;
String name = resultSet. getString ( "name" ) ;
int age = resultSet. getInt ( "age" ) ;
System . out. println ( id + " " + name + " " + age) ;
}
} catch ( Exception e) {
throw new RuntimeException ( e) ;
} finally {
JDBCUtils . closeResultSet ( resultSet) ;
JDBCUtils . closeConnection ( connection) ;
JDBCUtils . closeStatement ( preparedStatement) ;
}
}
}
public class JdbcTest {
public static void main ( String [ ] args) throws ClassNotFoundException {
batchInsertTest ( ) ;
}
public static void batchInsertTest ( ) {
List < Map < String , Object > > paramMapList = new ArrayList < > ( ) ;
Map < String , Object > paramMap = new HashMap < > ( ) ;
paramMap. put ( "name" , "姜科" ) ;
paramMap. put ( "age" , 18 ) ;
Map < String , Object > paramMap2 = new HashMap < > ( ) ;
paramMap2. put ( "name" , "老朱" ) ;
paramMap2. put ( "age" , 20 ) ;
paramMapList. add ( paramMap) ;
paramMapList. add ( paramMap2) ;
JDBCUtils . batchInsert ( paramMapList, 2 ) ;
JDBCUtils . selectAll ( ) ;
}
public static void insertTest ( ) {
Map < String , Object > paramMap = new HashMap < > ( ) ;
paramMap. put ( "name" , "测试" ) ;
paramMap. put ( "age" , 18 ) ;
JDBCUtils . insert ( paramMap) ;
JDBCUtils . selectAll ( ) ;
}
public static void updateTest ( ) {
Map < String , Object > paramMap = new HashMap < > ( ) ;
paramMap. put ( "name" , "zhangtao" ) ;
paramMap. put ( "age" , 100 ) ;
paramMap. put ( "id" , 1 ) ;
JDBCUtils . update ( paramMap) ;
JDBCUtils . selectAll ( ) ;
}
public static void deleteTest ( ) {
Map < String , Object > paramMap = new HashMap < > ( ) ;
paramMap. put ( "id" , 9999004 ) ;
JDBCUtils . delete ( paramMap) ;
JDBCUtils . selectAll ( ) ;
}
}