src 下的 jdbc.properties 文件
user=root
password=123456
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements = true
driverClass=com.mysql.cj.jdbc.Driver
com.atguigu1.transaction包
package com.atguigu1.transaction;
import java.sql.Connection;
import org.junit.Test;
import com.atguigu1.util.JDBCUtils;
public class ConnectionTest {
// 将 jdbc.properties 文件放入 src 目录下
@Test
public void testGetConnection() throws Exception {
Connection conn = JDBCUtils.getConnection();
System.out.println(conn);
}
}
package com. atguigu1. transaction ;
import java. lang. reflect. Field ;
import java. sql. Connection ;
import java. sql. PreparedStatement ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
import org. junit. Test ;
import com. atguigu1. util. JDBCUtils ;
import com. mysql. cj. jdbc. result. ResultSetMetaData ;
public class TransactionTest {
@Test
public void testUpdate ( ) {
String sql1 = " update user_table set balance = balance - 100 where user = ?" ;
update ( sql1, "AA" ) ;
System . out. println ( 10 / 0 ) ;
String sql2 = " update user_table set balance = balance + 100 where user = ?" ;
update ( sql2, "BB" ) ;
System . out. println ( "转账成功" ) ;
}
public int update ( String sql, Object . . . args) {
Connection conn = null ;
PreparedStatement ps = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
return ps. executeUpdate ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
conn. setAutoCommit ( true ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
JDBCUtils . closeResource ( conn, ps) ;
}
return 0 ;
}
@Test
public void testUpdateWithTx ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
System . out. println ( conn. getAutoCommit ( ) ) ;
conn. setAutoCommit ( false ) ;
String sql1 = " update user_table set balance = balance - 100 where user = ?" ;
update ( conn, sql1, "AA" ) ;
System . out. println ( 10 / 0 ) ;
String sql2 = " update user_table set balance = balance + 100 where user = ?" ;
update ( conn, sql2, "BB" ) ;
System . out. println ( "转账成功" ) ;
conn. commit ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
try {
conn. rollback ( ) ;
} catch ( SQLException e1) {
e1. printStackTrace ( ) ;
}
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
public int update ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
return ps. executeUpdate ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps) ;
}
return 0 ;
}
@Test
public void testTransactionSelect ( ) throws Exception {
Connection conn = JDBCUtils . getConnection ( ) ;
System . out. println ( conn. getTransactionIsolation ( ) ) ;
conn. setTransactionIsolation ( Connection . TRANSACTION_READ_COMMITTED) ;
conn. setAutoCommit ( false ) ;
String sql = "select user,password,balance from user_table where user = ?" ;
User user = getInstance ( conn, User . class , sql, "CC" ) ;
System . out. println ( user) ;
}
@Test
public void testTransactionUpdate ( ) throws Exception {
Connection conn = JDBCUtils . getConnection ( ) ;
conn. setAutoCommit ( false ) ;
String sql = "update user_table set balance = ? where user = ?" ;
update ( conn, sql, 5000 , "CC" ) ;
Thread . sleep ( 15000 ) ;
System . out. println ( "修改结束" ) ;
}
public < T > T getInstance ( Connection conn, Class < T > clazz, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
ResultSetMetaData rsmd = ( ResultSetMetaData ) rs. getMetaData ( ) ;
int columnCount = rsmd. getColumnCount ( ) ;
if ( rs. next ( ) ) {
T t = clazz. newInstance ( ) ;
for ( int i = 0 ; i < columnCount; i++ ) {
Object columValue = rs. getObject ( i + 1 ) ;
String columnLabel = rsmd. getColumnLabel ( i + 1 ) ;
Field field = clazz. getDeclaredField ( columnLabel) ;
field. setAccessible ( true ) ;
field. set ( t, columValue) ;
}
return t;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
}
package com. atguigu1. transaction ;
public class User {
private String user;
private String password;
private int balance;
public User ( ) {
super ( ) ;
}
public User ( String user, String password, int balance) {
super ( ) ;
this . user = user;
this . password = password;
this . balance = balance;
}
public String getUser ( ) {
return user;
}
public void setUser ( String user) {
this . user = user;
}
public String getPassword ( ) {
return password;
}
public void setPassword ( String password) {
this . password = password;
}
public int getBalance ( ) {
return balance;
}
public void setBalance ( int balance) {
this . balance = balance;
}
@Override
public String toString ( ) {
return "User [user=" + user + ", password=" + password + ", balance=" + balance + "]" ;
}
}
com.atguigu1.util 包
package com. atguigu1. util ;
import java. io. InputStream ;
import java. sql. Connection ;
import java. sql. DriverManager ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
import java. sql. Statement ;
import java. util. Properties ;
public class JDBCUtils {
public static Connection getConnection ( ) throws Exception {
InputStream is = ClassLoader . getSystemClassLoader ( ) . getResourceAsStream ( "jdbc.properties" ) ;
Properties pros = new Properties ( ) ;
pros. load ( is) ;
String user = pros. getProperty ( "user" ) ;
String password = pros. getProperty ( "password" ) ;
String url = pros. getProperty ( "url" ) ;
String driverClass = pros. getProperty ( "driverClass" ) ;
Class . forName ( driverClass) ;
Connection conn = DriverManager . getConnection ( url, user, password) ;
return conn;
}
public static void closeResource ( Connection conn, Statement ps) {
try {
if ( ps != null )
ps. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
if ( conn != null )
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
public static void closeResource ( Connection conn, Statement ps, ResultSet rs) {
try {
if ( ps != null )
ps. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
if ( conn != null )
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
if ( rs != null )
rs. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
com.atguigu2.bean 包
package com. atguigu2. bean ;
import java. sql. Date ;
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer ( ) {
super ( ) ;
}
public Customer ( int id, String name, String email, Date birth) {
super ( ) ;
this . id = id;
this . name = name;
this . email = email;
this . birth = birth;
}
public int getId ( ) {
return id;
}
public void setId ( int id) {
this . id = id;
}
public String getName ( ) {
return name;
}
public void setName ( String name) {
this . name = name;
}
public String getEmail ( ) {
return email;
}
public void setEmail ( String email) {
this . email = email;
}
public Date getBirth ( ) {
return birth;
}
public void setBirth ( Date birth) {
this . birth = birth;
}
@Override
public String toString ( ) {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]" ;
}
}
package com. atguigu2. bean ;
import java. sql. Date ;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order ( ) {
super ( ) ;
}
public Order ( int orderId, String orderName, Date orderDate) {
super ( ) ;
this . orderId = orderId;
this . orderName = orderName;
this . orderDate = orderDate;
}
public int getOrderId ( ) {
return orderId;
}
public void setOrderId ( int orderId) {
this . orderId = orderId;
}
public String getOrderName ( ) {
return orderName;
}
public void setOrderName ( String orderName) {
this . orderName = orderName;
}
public Date getOrderDate ( ) {
return orderDate;
}
public void setOrderDate ( Date orderDate) {
this . orderDate = orderDate;
}
@Override
public String toString ( ) {
return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]" ;
}
}
com.atguigu2.dao 包
package com. atguigu2. dao ;
import java. lang. reflect. Field ;
import java. sql. Connection ;
import java. sql. PreparedStatement ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
import java. util. ArrayList ;
import java. util. List ;
import com. atguigu1. util. JDBCUtils ;
import com. mysql. cj. jdbc. result. ResultSetMetaData ;
public abstract class BaseDAO {
public int update ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
return ps. executeUpdate ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps) ;
}
return 0 ;
}
public < T > T getInstance ( Connection conn, Class < T > clazz, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
ResultSetMetaData rsmd = ( ResultSetMetaData ) rs. getMetaData ( ) ;
int columnCount = rsmd. getColumnCount ( ) ;
if ( rs. next ( ) ) {
T t = clazz. newInstance ( ) ;
for ( int i = 0 ; i < columnCount; i++ ) {
Object columValue = rs. getObject ( i + 1 ) ;
String columnLabel = rsmd. getColumnLabel ( i + 1 ) ;
Field field = clazz. getDeclaredField ( columnLabel) ;
field. setAccessible ( true ) ;
field. set ( t, columValue) ;
}
return t;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
public < T > List < T > getForList ( Connection conn, Class < T > clazz, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
ResultSetMetaData rsmd = ( ResultSetMetaData ) rs. getMetaData ( ) ;
int columnCount = rsmd. getColumnCount ( ) ;
ArrayList < T > list = new ArrayList < T > ( ) ;
while ( rs. next ( ) ) {
T t = clazz. newInstance ( ) ;
for ( int i = 0 ; i < columnCount; i++ ) {
Object columValue = rs. getObject ( i + 1 ) ;
String columnLabel = rsmd. getColumnLabel ( i + 1 ) ;
Field field = clazz. getDeclaredField ( columnLabel) ;
field. setAccessible ( true ) ;
field. set ( t, columValue) ;
}
list. add ( t) ;
}
return list;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
public < E > E getValue ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i + 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
if ( rs. next ( ) ) {
return ( E ) rs. getObject ( 1 ) ;
}
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
}
package com. atguigu2. dao ;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import com. atguigu2. bean. Customer ;
public interface CustomerDAO {
void insert ( Connection conn, Customer cust) ;
void deleteById ( Connection conn, int id) ;
void update ( Connection conn, Customer cust) ;
Customer getCustomerById ( Connection conn, int id) ;
List < Customer > getAll ( Connection conn) ;
Long getCount ( Connection conn) ;
Date getMaxBirth ( Connection conn) ;
}
package com. atguigu2. dao ;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import com. atguigu2. bean. Customer ;
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
@Override
public void insert ( Connection conn, Customer cust) {
String sql = "insert into customers(name,email,birth)values(?,?,?)" ;
update ( conn, sql, cust. getName ( ) , cust. getEmail ( ) , cust. getBirth ( ) ) ;
}
@Override
public void deleteById ( Connection conn, int id) {
String sql = "delete from customers where id = ?" ;
update ( conn, sql, id) ;
}
@Override
public void update ( Connection conn, Customer cust) {
String sql = "update customers set name = ?,email = ?,birth = ? where id = ?" ;
update ( conn, sql, cust. getName ( ) , cust. getEmail ( ) , cust. getBirth ( ) , cust. getId ( ) ) ;
}
@Override
public Customer getCustomerById ( Connection conn, int id) {
String sql = "select id,name,email,birth from customers where id = ?" ;
Customer customer = getInstance ( conn, Customer . class , sql, id) ;
return customer;
}
@Override
public List < Customer > getAll ( Connection conn) {
String sql = "select id,name,email,birth from customers" ;
List < Customer > list = getForList ( conn, Customer . class , sql) ;
return list;
}
@Override
public Long getCount ( Connection conn) {
String sql = "select count(*) from customers" ;
return getValue ( conn, sql) ;
}
@Override
public Date getMaxBirth ( Connection conn) {
String sql = "select max(birth) from customers" ;
return getValue ( conn, sql) ;
}
}
com.atguigu2.dao.junit 包
package com. atguigu2. dao. junit ;
import static org. junit. Assert . *;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import org. junit. Test ;
import com. atguigu1. util. JDBCUtils ;
import com. atguigu2. bean. Customer ;
import com. atguigu2. dao. CustomerDAOImpl ;
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl ( ) ;
@Test
public void testInsert ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = new Customer ( 1 , "于小飞" , "xiaofei@126.com" , new Date ( 23453453232L ) ) ;
dao. insert ( conn, cust) ;
System . out. println ( "添加成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testDeleteById ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
dao. deleteById ( conn, 13 ) ;
System . out. println ( "删除成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testUpdateConnectionCustomer ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = new Customer ( 18 , "贝多芬" , "beiduofen@126.com" , new Date ( 3425214134L ) ) ;
dao. update ( conn, cust) ;
System . out. println ( "修改成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetCustomerById ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = dao. getCustomerById ( conn, 19 ) ;
System . out. println ( cust) ;
System . out. println ( "添加成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetAll ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
List < Customer > list = dao. getAll ( conn) ;
list. forEach ( System . out:: println ) ;
System . out. println ( "" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetCount ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Long count = dao. getCount ( conn) ;
System . out. println ( "表中的记录数为: " + count) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetMaxBirth ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Date maxBirth = dao. getMaxBirth ( conn) ;
System . out. println ( "最大的生日为: " + maxBirth) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
}
com.atguigu3.dao 包
package com. atguigu3. dao ;
import java. lang. reflect. Field ;
import java. lang. reflect. ParameterizedType ;
import java. lang. reflect. Type ;
import java. sql. Connection ;
import java. sql. PreparedStatement ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
import java. util. ArrayList ;
import java. util. List ;
import com. atguigu1. util. JDBCUtils ;
import com. mysql. cj. jdbc. result. ResultSetMetaData ;
public abstract class BaseDAO < T > {
private Class < T > clazz = null ;
{
Type genericSuperclass = this . getClass ( ) . getGenericSuperclass ( ) ;
ParameterizedType paramType = ( ParameterizedType ) genericSuperclass;
Type [ ] typeArguments = paramType. getActualTypeArguments ( ) ;
clazz = ( Class < T > ) typeArguments[ 0 ] ;
}
public int update ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
return ps. executeUpdate ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps) ;
}
return 0 ;
}
public T getInstance ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
ResultSetMetaData rsmd = ( ResultSetMetaData ) rs. getMetaData ( ) ;
int columnCount = rsmd. getColumnCount ( ) ;
if ( rs. next ( ) ) {
T t = clazz. newInstance ( ) ;
for ( int i = 0 ; i < columnCount; i++ ) {
Object columValue = rs. getObject ( i + 1 ) ;
String columnLabel = rsmd. getColumnLabel ( i + 1 ) ;
Field field = clazz. getDeclaredField ( columnLabel) ;
field. setAccessible ( true ) ;
field. set ( t, columValue) ;
}
return t;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
public List < T > getForList ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i+ 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
ResultSetMetaData rsmd = ( ResultSetMetaData ) rs. getMetaData ( ) ;
int columnCount = rsmd. getColumnCount ( ) ;
ArrayList < T > list = new ArrayList < T > ( ) ;
while ( rs. next ( ) ) {
T t = clazz. newInstance ( ) ;
for ( int i = 0 ; i < columnCount; i++ ) {
Object columValue = rs. getObject ( i + 1 ) ;
String columnLabel = rsmd. getColumnLabel ( i + 1 ) ;
Field field = clazz. getDeclaredField ( columnLabel) ;
field. setAccessible ( true ) ;
field. set ( t, columValue) ;
}
list. add ( t) ;
}
return list;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
public < E > E getValue ( Connection conn, String sql, Object . . . args) {
PreparedStatement ps = null ;
ResultSet rs = null ;
try {
ps = conn. prepareStatement ( sql) ;
for ( int i = 0 ; i < args. length; i++ ) {
ps. setObject ( i + 1 , args[ i] ) ;
}
rs = ps. executeQuery ( ) ;
if ( rs. next ( ) ) {
return ( E ) rs. getObject ( 1 ) ;
}
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( null , ps, rs) ;
}
return null ;
}
}
package com. atguigu3. dao ;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import com. atguigu2. bean. Customer ;
public interface CustomerDAO {
void insert ( Connection conn, Customer cust) ;
void deleteById ( Connection conn, int id) ;
void update ( Connection conn, Customer cust) ;
Customer getCustomerById ( Connection conn, int id) ;
List < Customer > getAll ( Connection conn) ;
Long getCount ( Connection conn) ;
Date getMaxBirth ( Connection conn) ;
}
package com. atguigu3. dao ;
import java. lang. reflect. ParameterizedType ;
import java. lang. reflect. Type ;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import com. atguigu2. bean. Customer ;
public class CustomerDAOImpl extends BaseDAO < Customer > implements CustomerDAO {
@Override
public void insert ( Connection conn, Customer cust) {
String sql = "insert into customers(name,email,birth)values(?,?,?)" ;
update ( conn, sql, cust. getName ( ) , cust. getEmail ( ) , cust. getBirth ( ) ) ;
}
@Override
public void deleteById ( Connection conn, int id) {
String sql = "delete from customers where id = ?" ;
update ( conn, sql, id) ;
}
@Override
public void update ( Connection conn, Customer cust) {
String sql = "update customers set name = ?,email = ?,birth = ? where id = ?" ;
update ( conn, sql, cust. getName ( ) , cust. getEmail ( ) , cust. getBirth ( ) , cust. getId ( ) ) ;
}
@Override
public Customer getCustomerById ( Connection conn, int id) {
String sql = "select id,name,email,birth from customers where id = ?" ;
Customer customer = getInstance ( conn, sql, id) ;
return customer;
}
@Override
public List < Customer > getAll ( Connection conn) {
String sql = "select id,name,email,birth from customers" ;
List < Customer > list = getForList ( conn, sql) ;
return list;
}
@Override
public Long getCount ( Connection conn) {
String sql = "select count(*) from customers" ;
return getValue ( conn, sql) ;
}
@Override
public Date getMaxBirth ( Connection conn) {
String sql = "select max(birth) from customers" ;
return getValue ( conn, sql) ;
}
}
com.atguigu3.dao.junit 包
package com. atguigu3. dao. junit ;
import static org. junit. Assert . *;
import java. sql. Connection ;
import java. sql. Date ;
import java. util. List ;
import org. junit. Test ;
import com. atguigu1. util. JDBCUtils ;
import com. atguigu2. bean. Customer ;
import com. atguigu3. dao. CustomerDAOImpl ;
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl ( ) ;
@Test
public void testInsert ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = new Customer ( 1 , "于小飞" , "xiaofei@126.com" , new Date ( 23453453232L ) ) ;
dao. insert ( conn, cust) ;
System . out. println ( "添加成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testDeleteById ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
dao. deleteById ( conn, 13 ) ;
System . out. println ( "删除成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testUpdateConnectionCustomer ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = new Customer ( 18 , "贝多芬" , "beiduofen@126.com" , new Date ( 3425214134L ) ) ;
dao. update ( conn, cust) ;
System . out. println ( "修改成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetCustomerById ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Customer cust = dao. getCustomerById ( conn, 19 ) ;
System . out. println ( cust) ;
System . out. println ( "添加成功" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetAll ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
List < Customer > list = dao. getAll ( conn) ;
list. forEach ( System . out:: println ) ;
System . out. println ( "" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetCount ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Long count = dao. getCount ( conn) ;
System . out. println ( "表中的记录数为: " + count) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
@Test
public void testGetMaxBirth ( ) {
Connection conn = null ;
try {
conn = JDBCUtils . getConnection ( ) ;
Date maxBirth = dao. getMaxBirth ( conn) ;
System . out. println ( "最大的生日为: " + maxBirth) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . closeResource ( conn, null ) ;
}
}
}