dbutils操作sqlserver数据库
1、引入两个包
dbutils包：commons-dbutils-1.7.jar http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi
sqlserver驱动包:sqljdbc42.jar
2、写dbutils的工具类:
-
import java.sql.Connection;
-
import java.sql.DriverManager;
-
import java.sql.ResultSet;
-
import java.sql.SQLException;
-
import java.sql.Statement;
-
public class DbUtils {
-
// 使用静态块加载驱动程序
-
static {
-
try {
-
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
-
} catch (ClassNotFoundException e) {
-
e.printStackTrace();
-
}
-
}
-
// 定义一个获取数据库连接的方法
-
public static Connection getConnection() {
-
Connection conn = null;
-
try {
-
conn = DriverManager.getConnection(“jdbc:sqlserver://58.214.246.30:11433;DatabaseName=IntelGrain_DATA_2016_cx”,
-
“sa”, “zhiyuan@111″);
-
} catch (SQLException e) {
-
e.printStackTrace();
-
System.out.println(“获取连接失败”);
-
}
-
return conn;
-
}
-
// 关闭数据库连接
-
public static void close(ResultSet rs, Statement stat, Connection conn) {
-
try {
-
if (rs != null)
-
rs.close();
-
if (stat != null)
-
stat.close();
-
if (conn != null)
-
conn.close();
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}
-
}
-
}
3、进行增删改操作
-
package com.vae.dao;
-
import java.sql.SQLException;
-
import java.util.List;
-
import org.apache.commons.dbutils.QueryRunner;
-
import org.apache.commons.dbutils.handlers.BeanHandler;
-
import org.apache.commons.dbutils.handlers.BeanListHandler;
-
import org.apache.commons.dbutils.handlers.ScalarHandler;
-
import com.util.db.DBUtils;
-
import com.vae.domain.Person;
-
public class PersonDaoImpl implements PersonDao {
-
private QueryRunner runner = null;//查询运行器
-
public PersonDaoImpl(){
-
runner = new QueryRunner();
-
}
-
//方法：向数据库中添加一条记录
-
@Override
-
public void add(Person p) throws SQLException {
-
String sql = “insert into person(name,age,description)values(?,?,?)”;
-
runner.update(DBUtils.getConnection(), sql, p.getName(), p.getAge(),p.getDescription());
-
}
-
//方法：根据id向数据库中修改某条记录
-
@Override
-
public void update(Person p) throws SQLException {
-
String sql = “update person set name=?,age=?,description=? where id=?”;
-
runner.update(DBUtils.getConnection(), sql, p.getName(),p.getAge(),p.getDescription(),p.getId());
-
}
-
//方法：根据id删除数据库中的某条记录
-
@Override
-
public void delete(int id) throws SQLException {
-
String sql = “delete from person where id=?”;
-
runner.update(DBUtils.getConnection(), sql, id);
-
}
-
//方法：使用BeanHandler查询一个对象
-
@Override
-
public Person findById(int id) throws SQLException {
-
String sql = “select name,age,description from person where id=?”;
-
Person p = runner.query(DBUtils.getConnection(), sql, new BeanHandler<Person>(Person.class),id);
-
return p;
-
}
-
//方法：使用BeanListHandler查询所有对象
-
@Override
-
public List<Person> findAll() throws SQLException {
-
String sql = “select name,age,description from person”;
-
List<Person> persons = runner.query(DBUtils.getConnection(), sql, new BeanListHandler<Person>(Person.class));
-
return persons;
-
}
-
//方法：使用ScalarHandler查询一共有几条记录
-
@Override
-
public long personCount()throws SQLException{
-
String sql = “select count(id) from person”;
-
return runner.query(DBUtils.getConnection(),sql, new ScalarHandler<Long>());
-
}
-
}