JDBC

来源：互联网发布：生存之民工知乎编辑：程序博客网时间：2024/05/17 08:04

JDBC基本概念

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

这里写图片描述

代码编写

编写连接数据库类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"));        }    }}

0 0