package com.test;
import java.io.ObjectInputStream.GetField;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.entity.Lilin_userinfo;
public class Test {
private static Log log = LogFactory.getLog(Test.class);
static{
//加载驱动
if(DbUtils.loadDriver( "com.mysql.jdbc.Driver" )){
log.info("连接成功!!!");
}else{
log.error("错误连接!!!");
}
}
public Connection getcon(){
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdbs", "root", "123456");
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public Lilin_userinfo getione() throws SQLException{
QueryRunner r = new QueryRunner();
Lilin_userinfo l =r.query( getcon(), "select * from lilin_userinfo where userid = ?", new BeanHandler<Lilin_userinfo>(Lilin_userinfo.class),1);
System.out.print(l.getUsername()+" ");
System.out.print(l.getUserid()+" ");
System.out.print(l.getPid() );
return l;
}
public List<Lilin_userinfo> getiall() throws SQLException{
QueryRunner r = new QueryRunner();
List<Lilin_userinfo> list = r.query(getcon(), "select * from lilin_userinfo", new BeanListHandler(Lilin_userinfo.class));
for (Lilin_userinfo l : list) {
System.out.print(l.getUsername()+" ");
System.out.print(l.getUserid()+" ");
System.out.print( l.getPid() );
System.out.println();
}
return list;
}
//成功返回一
public void insert() throws SQLException{
QueryRunner r = new QueryRunner();
Connection consss= getcon();
//consss.setAutoCommit(false);
String sql = "insert into lilin_userinfo(username,pid) values(?,?)";
Object[]pa = {"用户ds2x","2"};
System.out.println( r.update(consss,sql, pa) );
}
//成功返回一
public void bath() throws SQLException{
QueryRunner r = new QueryRunner();
Connection consss= getcon();
//没有事物控制
String sql = "insert into lilin_userinfo(username,pid) values(?,?)";
Object[][]pa = {{"用户三","2"},{"用户55","4"}};
System.out.println( r.batch(consss,sql, pa) );
}
//删除
public void delete() throws SQLException{
QueryRunner r = new QueryRunner();
Connection consss= getcon();
//没有事物控制
String sql = "delete from lilin_userinfo where userid >= ? ";
Object[]pa = {9};
System.out.println( r.update(consss,sql, pa) ); //返回受影响的行数
}
//事物控制测试
public void tran() throws SQLException {
Connection consss= getcon();
PreparedStatement ps = null;
try {
consss.setAutoCommit(false);
ps = consss.prepareStatement("insert into lilin_userinfo(username,pid) values(?,?)");
ps.setString(1, "用户的xx3d");
ps.setInt(2, 2);
ps.execute();
ps.setString(1, "用户的xx3");
ps.setInt(2, 21225);
ps.execute();
System.out.println("...");
consss.commit();
System.out.println("...");
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
try {
consss.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
DbUtils.close(ps);
DbUtils.close(consss);
}
}
public static void main(String[] args) throws SQLException {
Test t =new Test();
t.delete();
}
}
jar:
mysql-connector-java-5.1.24-bin.jar
commons-dbutils-1.5.jar
commons-logging-1.1.2.jar