使用数据库DBUtiles的QueryRunner实现增删改查

1、创建数据库

public class JdbcUtil {

    public static DataSource ds = null;
    static {
        try {
            //1.加载配置文件
            Properties p = new Properties();
            //获取字节码目录
            String path = JdbcUtil.class.getClassLoader().getResource("db.properties").getPath();
            FileInputStream in = new FileInputStream(path);
            p.load(in);
            //ds = BasicDataSourceFactory.createDataSource(p);
            //Alibaba德鲁伊连接池
            ds = DruidDataSourceFactory.createDataSource(p);
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    //获取数据源
    public static DataSource  getDataSource() {
        return ds;
    }
    public static Connection getConn() {
        try {
            // 2.连接数据
            return ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 关闭资源 
     */
    public static void close(Connection conn,Statement st,ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
}

2、把对象写入数据库

public class RegistServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {      
        //设置请求编码 与响应的编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        
        //获取参数
        //1.接收所有参数
            Map<String, String[]> parameterMap = request.getParameterMap();
            User u = new User();
            //2.把接收的参数封装成User对象
            try {
                BeanUtils.populate(u, parameterMap);
            } catch (IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            }
            //3.设置uid
            u.setUid(UUID.randomUUID().toString());
            
            //4.写入到数据库
            QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
            
            //判断该用户是否存在
            String sql2 = "select * from user where username = ?";
            User user2 = null;
            try {
                user2 = qr.query(sql2, new BeanHandler<User>(User.class), u.getUsername());
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            
            System.out.println("user2 == " + user2);
            ///说明用户名已存在
            if (user2 != null) {
                if (u.getUsername().equals(user2.getUsername())) {
                    response.getWriter().write("用户名已存在");
                    return;
                }
            }
            
            //插入数据
            String sql ="insert into user value(?,?,?,?)";
            try {
                qr.update(sql,u.getUid(),u.getUsername(),u.getPassword(),u.getPhone());
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
    }
}

3、使用sql语句操作

//学生对象
public class Student {
    Integer id;
    String name;
    Integer age;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
    
    
}

//接口类
public interface IStudentDao {
    /**
     * 保存一个学生
     */
    public void save(Student stu);
    /**
     * 删除学生
     */
    public void delete(int id);
    /**
     * 更新一个学生信息
     */
    public void update(int id,Student stu);
    /**
     * 获取指定学生
     */
    public Student get(int id);
    /**
     * 获取所有的学生
     */
    public List<Student> getAll();
    
    /**
     * 获取学生的总数
     */
    public Integer getCount();
    
}

//操作类
public class StudentDaoImpl implements IStudentDao {
    @Override
    public void save(Student stu) {
        String sql = "insert into student(name,age) values(?,?)";
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        try {
            qr.update(sql, stu.getName(),stu.getAge());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Override
    public void delete(int id) {
        // 3.创建语句
        String sql = "delete from student where id = ?";
        QueryRunner qr = new QueryRunner();
        Connection conn = JdbcUtil.getConn();
        
        try {
            qr.update(conn, sql, id);
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
    @Override
    public void update(int id, Student stu) {
        String sql = "update student set name=?, age=? where id =? ";
        QueryRunner qr = new QueryRunner();
        Connection conn = JdbcUtil.getConn();

        try {
            qr.update(conn, sql, stu.getName(), stu.getAge(), stu.getId());
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
    @Override
    public Student get(int id) {
        String sql = "select * from student where id = ?";
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        try {
            return qr.query(sql, new BeanHandler<Student>(Student.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public List<Student> getAll() {
        String sql = "select * from student ";
        Connection conn = JdbcUtil.getConn();
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        //获取全部记录
        try {
            List<Student> list = qr.query(conn, sql, new BeanListHandler<Student>(Student.class));
            conn.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public Integer getCount() {
        String sql = "select count(*) as count from student";
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        
        try {
            //必须long强转
            Integer num = ((Long) qr.query(sql, new ScalarHandler())).intValue();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        
    }
}

4、db.properties 的内容，一般放在资源文件夹下

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_db?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8
username=root
password="密码"
maxActive=8