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://127.0.0.1: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>());
    }

}