JDBC

JDBC基本概念

  JDBC(java data base connectivity,java数据库连接)，是一种用于执行sql语句的API，它为多种关系数据库提供了统一访问，它由一组java语言编写的类和接口组成。
  本文中是以连接mysql数据库为例。使用JDBC是需要在工程中导入对应的jar包的：下面是我在idea里工程中导入的jar包：

代码编写

1. 编写连接数据库类DBUtil:

package com.imooc.db;

import java.sql.*;

/**
 * Created by lizhi on 2016-10-20.
 */
public class DBUtil {
    private static final String URL = "";
    private static final String USER = "";
    private static final String Password = "";

    static {
            try{
                //加载驱动程序
                Class.forName("com.mysql.jdbc.Driver");
                //获得数据库连接
                Connection conn =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/User","root","root");
                //通过数据库的连接，操作数据库，实现增删改
            }catch (ClassNotFoundException e){
                e.printStackTrace();
            }catch (SQLException e){
                e.printStackTrace();
            }
    }

    public static Connection getConnection(){
        Connection conn = DBUtil.getConnection();
        return conn;
    }

}

2.在dao层编写数据库操作具体逻辑：

package com.imooc.dao;

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;
import org.omg.CORBA.Object;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by lizhi on 2016-10-22.
 */
public class GoddessDao {

    public void addGoddess(Goddess g) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = "" + "Insert into imooc_goddess" +
                "user_name,sex,age,birthday,email,mobile"
                +"creat_user,create_date,update_user,update_date,isdel"
                + "values("+
                "?,?,?,?,?,?,?,CURRENT_DATE ,?,CURRENT_DATE,?)";

        PreparedStatement ptmt =  conn.prepareStatement(sql);
        // 预编译

        ptmt.setString(1,g.getUser_name());
        ptmt.setInt(2,g.getSex());
        ptmt.setInt(3,g.getAge());
        ptmt.setDate(4,new Date(g.getBirthday().getTime()));

        // setDate是Javautil类型的，但是g.getBirthday是javasql类型的，需要转换
        ptmt.setString(5,g.getEmail());
        ptmt.setString(6,g.getMobile());
        ptmt.setString(7,g.getCreate_user());
        ptmt.setString(8,g.getUpdate_user());
        ptmt.setInt(9,g.getIsdel());
        ptmt.execute();

       // prepareStatement方法会将sql语句加载到驱动程序的执行程序中，但是并不直接执行,调用execute()时才真正执行
    }


    public void updateGoddess(Goddess g) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = " UPDATE imooc_goddess" +
                " user_name = ?,sex = ?,age = ?,birthday = ?,email = ?,mobile = ?"
                +" update_user = ?,update_date = CURRENT_DATE,isdel = ?"
                + " where id = ?";

        PreparedStatement ptmt =  conn.prepareStatement(sql);
        // 预编译

        ptmt.setString(1,g.getUser_name());
        ptmt.setInt(2,g.getSex());
        ptmt.setInt(3,g.getAge());
        ptmt.setDate(4,new Date(g.getBirthday().getTime()));
        // setDate是Javautil类型的，但是g.getBirthday是javasql类型的，需要转换，但是怎么查看类型呢？
        ptmt.setString(5,g.getEmail());
        ptmt.setString(6,g.getMobile());

        ptmt.setString(7,g.getUpdate_user());
        ptmt.setInt(8,g.getIsdel());
        ptmt.setInt(9,g.getId());
        ptmt.execute();
        // prepareStatement方法

    }

    public void delGoddess(int id) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = " DELETE from imooc_goddess" +
                     " where id = ?";

        PreparedStatement ptmt =  conn.prepareStatement(sql);
        // 预编译

        ptmt.setInt(1,id);
        ptmt.execute();
        // prepareStatement方法

    }

    @SuppressWarnings("JpaQueryApiInspection")
    public Goddess get(int id) throws Exception{
        Goddess g = null;
        Connection conn = DBUtil.getConnection();
        String sql = "" + "SELECT  * from imooc_goddess" +
                "where id = ?)";

        PreparedStatement ptmt =  conn.prepareStatement(sql);
        // 预编译

        ptmt.setInt(1,id);
        ResultSet rs =  ptmt.executeQuery();
        // 此处不能用execute()，因为execute()执行的是更改的操作
        while (rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));
            g.setSex(rs.getInt("sex"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setCreate_user(rs.getString("create_user "));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setCreate_user(rs.getString("create_user"));
            g.setIsdel(rs.getInt("isdel"));
        }
        return g;
    }

    public List<Goddess> query() throws Exception{
        Connection conn = DBUtil.getConnection();
        Statement stat =  conn.createStatement();
        ResultSet rs = stat.executeQuery("select user_name,age from imooc_goddess");

        List<Goddess> gs = new ArrayList<Goddess>();
        Goddess g = null;
        while (rs.next()){
            g = new Goddess();
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));
            gs.add(g);
        }
        return gs;
    }


    public List<Goddess> query(List<Map<String, Object>> params) throws Exception{
        List<Goddess> result = new ArrayList<Goddess>();
        Connection conn = DBUtil.getConnection();
        StringBuilder sb = new StringBuilder();

  如上所示，操作数据库的步骤为：

  首先创建一个Statement，要执行sql语句，就必须先获取到java.sql.Statement实例，Statement实例一共有三种类型:
1.执行静态的sql，通过Statement获得
2.执行动态的sql，通过PreparedStatement获得
3.执行数据库存储过程，通过CallableStatement获得
  Statement接口提供了三种执行SQL语句的方法：executeQuery 、executeUpdate 和execute
1.ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句，返回一个结果集（ResultSet）对象。
2.int executeUpdate(String sqlString)：用于执行INSERT、UPDATE或DELETE语句以及SQL DDL语句，如：CREATE TABLE和DROP TABLE等
3.execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的语句。

如果是存储过程的话，编写就简单很多，如下：

package com.imooc.dao;

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by lizhi on 2016-10-25.
 */
public class ProduceDao {

    public static int select_count() throws Exception{
        int count = 0;
        //获得连接
        Connection conn = DBUtil.getConnection();

        //获得callablestatment,括号内为存储过程名称
        CallableStatement cs = conn.prepareCall("call sp_select_count(?)");

        cs.registerOutParameter(1, Types.INTEGER);
        //执行存储过程
        cs.execute();

        //处理返回的结果：结果集，出参
        cs.getInt(1);
        return count;
    }


    public static List<Goddess>  select_filter(String sp_name) throws Exception{

        List<Goddess> result = new ArrayList<Goddess>();
        //获得连接
        Connection conn = DBUtil.getConnection();

        //获得callablestatment,括号内为存储过程名称
        CallableStatement cs = conn.prepareCall("call sp_select_filter(?)");

        cs.setString(1,sp_name);
        //执行存储过程
        cs.execute();

        //处理返回的结果：结果集，出参
        ResultSet rs = cs.getResultSet();
        Goddess g = null;
        while (rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));
            result.add(g);
        }
        return result;
    }




    public static void select_nofilter() throws SQLException {
        //获得连接
        Connection conn = DBUtil.getConnection();

        //获得callablestatment,括号内为存储过程名称
        CallableStatement cs = conn.prepareCall("call sp_select_nofilter");

        //执行存储过程
        cs.execute();

        //处理返回的结果：结果集，出参
        ResultSet rs = cs.getResultSet();
        while (rs.next()){
            System.out.println(rs.getString("user_name")+rs.getString("email")+
            rs.getString("mobile"));
        }
    }
}