考试JDBC项目速成

项目结果:

考试JDBC项目速成

构建对应数据库类型的类

样例的学生数据库比较简单,只有学号,姓名,年龄3个属性.对应的类也需要写出3种属性(用类的原因是后面可以包装成List操作方便)

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

/**
 *
 * @author 郑佳杰
 */
public class Student {

    private String sno;
    private String sname;
    private int sage;

    public Student(String sno, String sname, int sage) {
        this.sno = sno;
        this.sname = sname;
        this.sage = sage;
    }

    public Student() {
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }
}

访问数据库的通用读写方法类

首先要写出3个对象
考试JDBC项目速成
分别对应ado.net连接数据库的coon,cmd,dataset。
之后写

连接函数

 try {
            //1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接对象
            String url = "jdbc:mysql://localhost:3306/my_db?useUnicode=true&characterEncoding=utf-8";
            String user = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
//装载驱动语法记住!!
 Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接对象
//url记住!!
            String url = "jdbc:mysql://localhost:3306/my_db?useUnicode=true&characterEncoding=utf-8";
            String user = "root";
            String password = "123456";

关闭函数

比较简单

public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

增删改操作

public int executeUpdate(String sql, Object[] params) {
        this.getConnection();//前提保证数据库连接
        int result = 0;
        try {
            //3、创建prepareStatement对象
            pstmt = conn.prepareStatement(sql);//和ado.net给cmd附值查询字符串一个道理
            //4、为占位符赋值
            if (null != params) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);//Object是任何对象的父类,也就是说可以转换成任何的类型
                    //setObject下标从1开始
                }
            }
            //5、调用方法:执行sql语句
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, null);
        }
        return result;
    }

总体代码:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

/**
 *
 * @author 郑佳杰
 */
import java.sql.*;
public class Dbutils {

    protected Connection conn = null;
    protected PreparedStatement pstmt = null;
    protected ResultSet rs = null;

    /**
     * 获取连接对象
     * @return 连接对象
     */
    public Connection getConnection() {
        try {
            //1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接对象
            String url = "jdbc:mysql://localhost:3306/my_db?useUnicode=true&characterEncoding=utf-8";
            String user = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭资源
     *
     * @param conn
     * @param pstmt
     * @param rs
     */
    public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 增删改操作
     *
     * @param sql
     * @param params
     * @return 
     */
    public int executeUpdate(String sql, Object[] params) {
        this.getConnection();
        int result = 0;
        try {
            //3、创建prepareStatement对象
            pstmt = conn.prepareStatement(sql);
            //4、为占位符赋值
            if (null != params) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            //5、调用方法:执行sql语句
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, null);
        }
        return result;
    }

    /**
     * 查询方法
     *
     * @param sql
     * @param params
     * @return
     */
    public ResultSet executQuery(String sql, Object[] params) {
        this.getConnection();
        try {
            //3、创建prepareStatement对象
            pstmt = conn.prepareStatement(sql);
            //4、为占位符赋值
            if (null != params) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            //5、调用方法:执行sql语句
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //后面具体的查询方法还需要用到rs ,所以此处最后不能关闭数据流
        return rs;
    }
}

考试JDBC项目速成

上一篇:简单的数据库查询优化


下一篇:C# 实体类忽略某些属性字段不映射到数据库