关于JDBC的一些基础知识_jdbc proxy todo的区别-优快云博客

本文链接：https://blog.youkuaiyun.com/vizzyo/article/details/76729107

本文详细介绍JDBC连接数据库的方法，包括加载驱动、执行SQL语句、使用PreparedStatement防止SQL注入等核心内容，并介绍了如何通过配置文件简化数据库连接过程。此外，还探讨了如何利用JDBC进行日期时间类型转换、调用存储过程及存储大文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

JDBC

JDBC4驱动加载问题

JDK1.6之后JDBC就升级到了JDBC4，只要数据库生产商实现了JDBC4提供的接口，则在连接数据时，不需要显式加载驱动，系统自动加载

mysql驱动从5.1开始支持jdbc4

关于URL语法：jdbc:子协议://地址:端口号/数据库实例名

MySQL:jdbc:mysql://127.0.0.1:3306/test
sqlserver: jdbc:sqlserver://127.0.0.1:1433;databaseName=test
oracle: jdbc:oracle:thin:@127.0.0.1:1521:orcl

JDBC连接数据库：

public static void main(String[] args) throws ClassNotFoundException, SQLException {
           //1.加载数据库提供商提供的驱动
           Class.forName("com.mysql.jdbc.Driver");
           //DriverManager.registerDriver(new Driver());


           //2.获取数据库连接对象
           Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "123456");
         //Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");
           System.out.println(conn);


           //3.获取处理命令（用于处理SQL语句）
           Statement stmt = conn.createStatement();


           //4.发送执行sql命令
           //boolean f = stmt.execute("create table people(name varchar(16),id int ,sex varchar(8));");
           stmt.execute("insert into people values('吴为傻逼',1,'20')");


           //5.处理执行结果
           //System.out.println(f);


           //6.回收资源
           stmt.close();
           conn.close();

execute,executeUpdate,executeQuery区别？

excute用于执行任何的sql语句，返回布尔类型结果，如果被执行的sql语句为查询相关语句则会有ResultSet产生，从而返回true；其他非查询操作都会返回false

executeUpdate主要用于执行DML语句中的insert，delete，update操作，同时可以执行DDl（建表，建库）相关操作，如果执行DML中增删改操作时会返回操作影响的数据行数，如果是DDL操作则返回0.

JDBC实现增删改查：（Statement为不安全对象，容易产生sql注入）

public class JdbcCrud {
     /**
      * 向表中添加数据
      */
     public void insert(String name, int id, String sex) {
           Connection conn = null;
           Statement stmt = null;
           try {
                // 加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                // 获取连接
                conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "123456");
                // 创建处理命令
                stmt = conn.createStatement();
                // 执行sql语句
                int i = stmt.executeUpdate("insert into people(name,id,sex) values('" + name + "','" + id + "','" + sex + "')");
                //处理结果
                if(i > 0)
                {
                     System.out.println("添加成功，影响数据行数："+i);
                }
           } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }finally{
                try {
                     if(stmt != null) stmt.close();
                     if(conn != null) conn.close();
                } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
                }
           }
     }
     
     //查询所有操作
     public void queryAll()
     {
           Connection conn = null;
           Statement stmt = null;
           ResultSet rs = null;
           try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");
                stmt = conn.createStatement();
                
                //执行查询操作获取结果集
                rs = stmt.executeQuery("select * from people");
                //取出结果集中的数据
                while(rs.next()){
                     String name = rs.getString("name");
                     int id = rs.getInt("id");
                     String sex = rs.getString("sex");
                     
                     System.out.println(name+"-----"+id+"-----"+sex);
                }
           } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }finally{
                try {
                     if(rs!=null)rs.close();
                     if(stmt!=null)stmt.close();
                     if(conn!=null)conn.close();
                } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
                }
           }
     }
     
     //根据用户id删除指定用户
     
     public void deleteById(int id)
     {
           Connection conn = null;
           Statement stmt = null;
           try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");
                stmt = conn.createStatement();
                int i = stmt.executeUpdate("delete from people where id =" + id );
                System.out.println("删除成功，影响数据行数："+i);
           } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }finally {
                try {
                     if(stmt!=null)stmt.close();
                     if(conn!=null)conn.close();
                } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
                }
           }
     }
}

PreparedStatement（可防止SQL注入）

public class JdbcSafe
 {
     public void add(String name,int id,String password)
     {
           Connection conn = null;
           PreparedStatement ps = null;
           
           try {
                Class.forName("com.mysql.jdbc.Driver");
                conn =
 DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "123456");
                ps = conn.prepareStatement("insert
 into tbuser(name,id,password) values(?,?,?)");
                ps.setString(1, name);
                ps.setInt(2, id);
                ps.setString(3, password);
                
                int i =ps.executeUpdate();
                System.out.println("影响记录行数："+i);
           } catch (ClassNotFoundException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           } catch (SQLException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           }finally{
                try {
                     ps.close();
                     conn.close();
                } catch (SQLException e)
 {
                     // TODO Auto-generated
 catch block
                     e.printStackTrace();
                }
           }
     }

JDBC连接封装和配置文件

//配置文件
####mysql connection
 info####
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/mydb
user = root
password = 123456




public class DBConnection
 {
     
     //声明连接数据库的基本参数
     private static 
 String DRIVER = "com.mysql.jdbc.Driver";
     private static 
 String URL = "jdbc:mysql://127.0.0.1:3306/mydb";
     private static 
 String USER = "root";
     private static 
 String PASSWORD = "123456";
     
     //在静态块中加载驱动，防止反复加载
     static{
           try {
                //获取属性文件
                Properties pros =
 System.getProperties();
                //加载指定属性文件
                pros.load(new FileInputStream("src/jdbc.properties"));
                DRIVER = pros.getProperty("driver");
                URL = pros.getProperty("url");
                USER = pros.getProperty("user");
                PASSWORD = pros.getProperty("password");
                
                Class.forName(DRIVER);
           } catch (ClassNotFoundException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           } catch (FileNotFoundException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           } catch (IOException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           }
     }
     
     //封装获取连接方法
     public static Connection
 getConn()
     {
           try {
                return DriverManager.getConnection(URL,USER,PASSWORD);
           } catch (SQLException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           }
           return null;
     }
     
     //封装资源回收的方法
     public static void close(ResultSet rs,Statement stmt,Connection conn)
     {
           try {
                if(rs != null)rs.close();
                if(stmt!=null)stmt.close();
                if(conn!=null)conn.close();
           } catch (SQLException e)
 {
                // TODO Auto-generated
 catch block
                e.printStackTrace();
           }
     }
     
}

JDBC日期时间类型转换

public void add(String content,Date lasttime) throws SQLException{
           
           Connection conn =
 DBConnection.getConn();
           PreparedStatement ps = conn.prepareStatement("insert
 into daily(content,lasttime) values(?,?)");
           ps.setString(1, content);
           ps.setTimestamp(2, new Timestamp(lasttime.getTime()));
           int i = ps.executeUpdate();
           System.out.println(i);
     }
     
     public void queryAll() throws SQLException{
           Connection conn =
 DBConnection.getConn();
           PreparedStatement ps = conn.prepareStatement("select
 content,submittime,lasttime from daily");
           ResultSet rs = ps.executeQuery();
           while(rs.next())
           {
                String content = rs.getString("content");
                Date d1 = rs.getTimestamp("submittime");
                Date d2 = rs.getTimestamp("lasttime");
                System.out.println(content+"--"+d1+"--"+d2);
           }
     
     }

简单封装：查询数据库中某个表中的所有数据

public List<Object[]> query() throws SQLException{
           PreparedStatement ps =
 DBConnection.getConn().prepareStatement("select
 * from department limit 0,10");
           ResultSet rs = ps.executeQuery();
           ResultSetMetaData rsmd = rs.getMetaData();
           int count = rsmd.getColumnCount();
           List<Object[]> list = new ArrayList<>();
           while(rs.next()){
                //每读取到一行记录声明一个数组
                Object[] objs = new Object[count];
                for (int i =
 1; i <= count; i++)
 {
                     //获取列标签名(别名，若不存在则用列名)
                     String label = rsmd.getColumnLabel(i);
                     //获取列名称
                     String cname = rsmd.getColumnName(i);
                     //获取列数据类型
     //              int type
 = rsmd.getColumnType(i);
     //              if(type
 == java.sql.Types.INTEGER){
     //                   int num =
 rs.getInt(label);
     //                   System.out.print(num+" 
 ");
     //              }else
 if(type == java.sql.Types.VARCHAR){
     //                   String
 value = rs.getString(label);
     //                   System.out.print(value+" 
 ");
     //              }
                     //获取一列数据
                     Object obj = rs.getObject(label);
                     //将列数据装入数组中
                     objs[i-1]
 = obj;
                }
                //将装有多列数据的一行结果装入集合
                list.add(objs);
           }
           return list;
     }

JDBC存储过程调用

//不带参数的存储过程调用
Connection conn =
 DBConnection.getConn();
CallableStatement cs = conn.prepareCall("{call pro_01}");


//带参数的存储过程调用（分页）
//DTO
public class PageUtils
 {
     private int currentPage;   //当前页
     private int pageSize;      //每页大小
     private String tableName;  //表名称
     private String selections; //查询列   
     private String condition;  //查询条件
     private String sortColumn; //排序列
     private String sortType;   //排序类型 asc desc
     
     private int totalNum;      //总记录数
     private int totalPage;          //总页码数
     private List<Object[]> datas;   //当前页数据
     
     public int getCurrentPage()
 {
           return currentPage;
     }
     public void setCurrentPage(int currentPage)
 {
           this.currentPage = currentPage;
     }
     public int getPageSize()
 {
           return pageSize;
     }
     public void setPageSize(int pageSize)
 {
           this.pageSize = pageSize;
     }
     public String
 getTableName() {
           return tableName;
     }
     public void setTableName(String tableName)
 {
           this.tableName = tableName;
     }
     public String
 getSelections() {
           return selections;
     }
     public void setSelections(String selections)
 {
           this.selections = selections;
     }
     public String
 getCondition() {
           return condition;
     }
     public void setCondition(String condition)
 {
           this.condition = condition;
     }
     public String
 getSortColumn() {
           return sortColumn;
     }
     public void setSortColumn(String sortColumn)
 {
           this.sortColumn = sortColumn;
     }
     public String
 getSortType() {
           return sortType;
     }
     public void setSortType(String sortType)
 {
           this.sortType = sortType;
     }
     public int getTotalNum()
 {
           return totalNum;
     }
     public void setTotalNum(int totalNum)
 {
           this.totalNum = totalNum;
     }
     public int getTotalPage()
 {
           return totalPage;
     }
     public void setTotalPage(int totalPage)
 {
           this.totalPage = totalPage;
     }
     public List<Object[]>
 getDatas() {
           return datas;
     }
     public void setDatas(List<Object[]> datas)
 {
           this.datas = datas;
     }
     
}


public PageUtils procPaging(PageUtils pu) throws SQLException{
           CallableStatement cs =
 getConn().prepareCall("{call sp_paging(?,?,?,?,?,?,?,?,?)}");
           cs.setInt(1, pu.getCurrentPage());
           cs.setInt(2, pu.getPageSize());
           cs.setString(3, pu.getTableName());
           cs.setString(4, pu.getSelections());
           cs.setString(5, pu.getCondition());
           cs.setString(6, pu.getSortColumn());
           cs.setString(7, pu.getSortType());
           
           //注册输出参数
           cs.registerOutParameter(8,
 java.sql.Types.INTEGER);
           cs.registerOutParameter(9,
 java.sql.Types.INTEGER);
           //执行存储过程
           cs.execute();
           //获取制定位置的输出参数值
           int totalNum = cs.getInt(8);
           int totalPage = cs.getInt(9);
           pu.setTotalNum(totalNum);
           pu.setTotalPage(totalPage);
           
           //声明用于存储查询结果的集合
           List<Object[]> datas = new ArrayList<>();
           
           //获取查询的结果集
           ResultSet rs = cs.getResultSet();
           ResultSetMetaData rsmd = rs.getMetaData();
           int count = rsmd.getColumnCount();
           while(rs.next()){
                Object[] obj = new Object[count];
                for(int i =
 0;i<count;i++){
                     //获取标签名称（可能是列名称）
                     String label = rsmd.getColumnLabel(i+1);
                     Object c = rs.getObject(label);
                     obj[i]
 = c;
                }
                datas.add(obj);
           }
           //将查询结果设置到PageUtils中
           pu.setDatas(datas);
           return pu;
     }

DTO：（Data Transfer Object）数据传输对象，用于在前后端（界面，数据库）之间进行数据传递

1.临时存储界面提交的数据，并将数据通过jdbc加入到数据库中（数据持久化）

2.取出数据库表中的数据，临时存储到对象，并运转到界面端展示

public void add(Emp emp) throws SQLException{
        String sql = "insert into employee(name,sex,tel,addr,email,zip,depno,birth) values(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = getConn().prepareStatement(sql);
        ps.setString(1,emp.getName());
        ps.setString(2,emp.getSex());
        ps.setString(3,emp.getTel());
        ps.setString(4,emp.getAddr());
        ps.setString(5,emp.getEmail());
        ps.setString(6,emp.getZip());
        ps.setInt(7, emp.getDepno());
        ps.setDate(8, new java.sql.Date(emp.getBirth().getTime()));
        int i = ps.executeUpdate();
        System.out.println("执行结果:"+i);
    }


public List<Emp> findAll() throws SQLException{
        List<Emp> list = new ArrayList<>();
        String sql = "select num,name,sex,tel,addr,email,zip,depno,birth from employee limit 0,10";
        PreparedStatement ps = getConn().prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        Emp emp = null;
        while(rs.next()){
            int num = rs.getInt("num");
            String name = rs.getString("name");
            String addr = rs.getString("addr");
            String email = rs.getString("email");
            String sex = rs.getString("sex");
            String tel = rs.getString("tel");
            String zip = rs.getString("zip");
            int depno = rs.getInt("depno");
            Date birth = rs.getDate("birth");
            
            emp = new Emp(name, addr, zip, email, tel, sex, birth, depno);
            emp.setNum(num);
            list.add(emp);
        }
        return list;
    }

分层思想：

视图层

业务逻辑层

数据持久层

DAO：（Data Access Object）数据访问对象，负责对数据库进行CRUD相关的访问操作，内部的每一个方法都是一个对于数据库的原子操作

public class UserDAO extends BaseConn {
    PreparedStatement ps;
    ResultSet rs;
    // 添加操作
    public boolean insert(User user) throws SQLException {
        ps = getConn().prepareStatement("insert into tbuser(username,password) values(?,?)");
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        int i = ps.executeUpdate();
        return i > 0 ? true : false;
    }
    // 删除操作
    public boolean delete(User user) throws SQLException {
        ps = getConn().prepareStatement("delete from tbuser where id=?");
        ps.setInt(1, user.getId());
        int i = ps.executeUpdate();
        return i > 0 ? true : false;
    }
    // 修改操作
    public boolean update(User user) throws SQLException {
        ps = getConn().prepareStatement("update tbuser set password=? where id=?");
        ps.setString(1, user.getPassword());
        ps.setInt(2, user.getId());
        int i = ps.executeUpdate();
        return i > 0 ? true : false;
    }
    // 查询所有
    public List<User> findAll() throws SQLException {
        List<User> list = new ArrayList<>();
        ps = getConn().prepareStatement("select * from tbuser");
        rs = ps.executeQuery();
        User user = null;
        while(rs.next()){
            user = new User(rs.getInt("id"),rs.getString("username"), rs.getString("password"));
            list.add(user);
        }
        return list;
    }
    // 根据id查询
    public User findById(int id) throws SQLException {
        ps = getConn().prepareStatement("select * from tbuser where id=?");
        ps.setInt(1, id);
        rs = ps.executeQuery();
        User user = null;
        if(rs.next()){
            user = new User(id,rs.getString("username"), rs.getString("password"));
        }
        return user;
    }
}

JDBC存储大文件

//存文件
public void insertClob(File file) throws SQLException, IOException{
           Connection conn = DBUtils.getConn();
           //获取文件字符输入流
           FileReader br = new FileReader(file);
           
           PreparedStatement ps = conn.prepareStatement("insert
 into tb_clob(fname,content) values(?,?)");
           ps.setString(1, file.getName());
           ps.setCharacterStream(2,br);
           if(ps.executeUpdate()
 > 0 ){
                System.out.println("写入完成！");
           }
           ps.close();
           br.close();
           conn.close();
     }


//取出文件
public void selectClob(int id) throws SQLException
     {
           Connection conn = DBUtils.getConn();
           PreparedStatement ps = conn.prepareStatement("select
 content from tb_clob where id=?");
           ps.setInt(1, id);
           ResultSet rs = ps.executeQuery();
           if(rs.next()){
                String content = rs.getString("content");
//              Reader reader = rs.getCharacterStream("content"); //如果需要将数据存储到文件中，则获取字符流
                System.out.println(content);
           }
     }


//存二进制文件
如果向MySQL中存储二进制文件时，数据库的字符集不能设置为GBK，要设置成UTF8
public void insertBlob(File file) throws SQLException, IOException
     {
           Connection conn = DBUtils.getConn();
           //获取文件的字节输入流
           FileInputStream fis = new FileInputStream(file);
           PreparedStatement ps = conn.prepareStatement("insert
 into tb_blob(fname,file) values(?,?)");
           ps.setString(1, file.getName());
           ps.setBinaryStream(2, fis); //设置二进制流
           if(ps.executeUpdate()
 > 0){
                System.out.println("文件写入完成！");
           }
           ps.close();
           fis.close();
           conn.close();
     }


//取二进制文件
public void selectBlob(int id,File dir) throws SQLException, IOException{
           Connection conn = DBUtils.getConn();
           PreparedStatement ps = conn.prepareStatement("select
 fname,file from tb_blob where id=?");
           ps.setInt(1, id);
           ResultSet rs = ps.executeQuery();
           if(rs.next()){
                //获取文件名
                String fname = rs.getString("fname");
                //获取文件的二进制流(输入)
                InputStream is = rs.getBinaryStream("file");
                //将文件名和目录组合为目标文件,并创建输出流
                FileOutputStream fos = new FileOutputStream(new File(dir,fname));
                
                BufferedInputStream bis = new BufferedInputStream(is);
                BufferedOutputStream bos = new BufferedOutputStream(fos);
                byte[] b = new byte[1024];
                int len =
 0;
                while((len = bis.read(b))
 != -1)
                {
                     bos.write(b,0,len);
                }
                bos.close();
                bis.close();
           }
           rs.close();
           ps.close();
           conn.close();
     }