jdbc学习笔记

服务器端jdbc的编写，主要还是照着代码打。

1、MySQL配置
在cmd开启了MySQL服务，用的是navicat管理数据库，简单明了，果然还是喜欢GUI，哈哈

2、代码编写

基本操作，包括构造方法中的注册驱动，获取数据库连接，关闭数据库连接

    public class JdbcUtils {
    //表示定义数据库的用户名
    private final String USERNAME = "root";
    //表示定义数据库的密码
    private final String PASSWORD = "123456";
    //表示数据库的驱动程序信息
    private final String DRIVER = "com.mysql.jdbc.Driver";
    //表示数据库的地址
    private final String URL = "jdbc:mysql://localhost:3306/mydb";
    //数据库的连接
    private Connection connection;
    //sql语句执行对象
    private PreparedStatement pstmt;
    //查询返回结果的集合
    private ResultSet resultSet;

    //构造方法，注册驱动并输出
    public JdbcUtils() {
        try {
            //注册驱动
            Class.forName(DRIVER);
            System.out.println("注册驱动成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得数据库的连接
     * @return 连接对象
     */
    public Connection getConnection() {
        try {
            connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭请求的方法
     */
    public void releaseConn() {
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

数据操作的方法，包括修改数据库信息，查询数据库的数据（单条查询，多条查询，单条反射机制查询，多条反射机制查询）

    /**
     * 修改数据库中数据的方法，通过占位符的形式修改
     * sql语句示例：
     * sql = "insert into usertest(user,pwd) values(?,?)";
     * @param sql sql语句
     * @param params 占位符
     * @return
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(String sql,
            List<Object> params) throws SQLException {
        boolean flag = false;
        //当用户执行操作的时候所影响数据库的行数
        int result = -1;
        //创建动态sql语句，不用createStatement因为效率不高
        pstmt = connection.prepareStatement(sql);
        //占位符的第一个位置
        int index = 1;
        //填充占位符
        if(params!=null && !params.isEmpty()) {
            for(int i=0;i<params.size();i++) {
                pstmt.setObject(index++,params.get(i));
            }
        }
        //获取操作影响的行数
        result = pstmt.executeUpdate();
        flag = result>0? true:false;
        return flag;
    }

    /**
     * 查询单条记录的方法
     * sql = "select * from usertest";
     * sql = "select * from usertest where id = ?";
     * @param sql
     * @param params
     * @return 存有记录键值对的map
     * @throws SQLException
     */
    public Map<String,Object> findSimpleResult(String sql,
            List<Object> params) throws SQLException {
        Map<String,Object> map = new HashMap<String,Object>();
        //创建动态sql语句
        pstmt = connection.prepareStatement(sql);
        //填充占位符
        int index = 1;
        if(params!=null && !params.isEmpty()) {
            for(int i=0;i<params.size();i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();//返回查询语句
        //把resultSet封装到map中
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();//获得列的长度
        if(resultSet.next()) {
            for(int i=0;i<col_len;i++) {
                String col_name = metaData.getColumnName(i+1);//获得列名
                Object col_value = resultSet.getObject(col_name);//获得列名所对的值
                if(col_value == null) {
                    col_value = "";
                }
                map.put(col_name, col_value);
            }
        }
        return map;
    }

    /**
     * 查询返回多条记录
     * sql = "select * from usertest";
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String,Object>> findMoreResult(String sql,
            List<Object> params) throws SQLException {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        //创建动态sql语句
        pstmt = connection.prepareStatement(sql);
        //填充占位符
        int index = 1;
        if(params!=null && !params.isEmpty()) {
            for(int i=0;i<params.size();i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();//获取列的数目
        while(resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for(int i=0;i<col_len;i++) {
                String col_name = metaData.getColumnName(i+1);//获得列名
                Object col_value = resultSet.getObject(col_name);//获得列名所对的值
                if(col_value == null) {
                    col_value = "";
                }
                map.put(col_name, col_value);
            }
            list.add(map);
        }
        return list;
    }

    //通过反射机制实现查询方法
    /**
     * 通过反射机制实现查询单条记录的方法
     * sql = "select * from usertest where id = ?";
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> T findSimpleRefResult(String sql,List<Object> params,
            Class<T> cls) throws Exception {
        T resultObject = null;
        //创建动态sql语句
        pstmt = connection.prepareStatement(sql);
        //填充占位符
        int index = 1;
        if(params!=null && !params.isEmpty()) {
            for(int i=0;i<params.size();i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();//获取结果集
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();//获取列数目
        //通过反射机制创建实例
        while(resultSet.next()) {
            resultObject = cls.newInstance();
            for(int i=0;i<col_len;i++) {
                String col_name = metaData.getColumnName(i+1);
                Object col_value = resultSet.getObject(col_name);
                if(col_value == null) {
                    col_value = "";
                }
                Field field = cls.getDeclaredField(col_name);
                field.setAccessible(true);//打开javabean的访问private访问权限
                field.set(resultObject, col_value);
            }
        }
        return resultObject;
    }

    /**
     * 通过反射机制实现查询多条记录的方法
     * sql = "select * from usertest";
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql,List<Object> params,
            Class<T> cls) throws Exception {
        List<T> list = new ArrayList<T>();
        //创建动态sql语句
        pstmt = connection.prepareStatement(sql);
        //填充占位符
        int index = 1;
        if(params!=null && !params.isEmpty()) {
            for(int i=0;i<params.size();i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();//获取结果集
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();//获取列数目
        //通过反射机制创建实例
        while(resultSet.next()) {
            T resultObject = cls.newInstance();
            for(int i=0;i<col_len;i++) {
                String col_name = metaData.getColumnName(i+1);
                Object col_value = resultSet.getObject(col_name);
                if(col_value == null) {
                    col_value = "";
                }
                Field field = cls.getDeclaredField(col_name);
                field.setAccessible(true);//打开javabean的访问private访问权限
                field.set(resultObject, col_value);
            }
            list.add(resultObject);
        }

        return list;
    }

反射机制中我定义的一个简单类

public class UserInfo implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -301042178920002421L;
    private int id;
    private String user;
    private String pwd;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUser() {
        return user;
    }
    public void setUser(String user) {
        this.user = user;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    @Override
    public String toString() {
        return "UserInfo [id=" + id + ", user=" + user + ", pwd=" + pwd + "]";
    }
}

测试的Main方法（每一段分别进行测试）

    public static void main(String[] args) {
        JdbcUtils jdbcUtils = new JdbcUtils();
        jdbcUtils.getConnection();

        String sql = "insert into usertest(user,pwd) values(?,?)";
        List<Object> list = new ArrayList<Object>();
        list.add("Rose");
        list.add("34567");
        try {
            jdbcUtils.updateByPreparedStatement(sql, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.releaseConn();
        }

        String sql = "select * from usertest";
        try {
            Map<String, Object> map = jdbcUtils.findSimpleResult(sql, null);
            System.out.println(map);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.releaseConn();
        }

        String sql = "select * from usertest where id = ?";
        List<Object> params = new ArrayList<Object>();
        params.add(3);
        Map<String, Object> map = null;
        try {
            map = jdbcUtils.findSimpleResult(sql, params);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.releaseConn();
        }

        String sql = "select * from usertest";
        try {
            List<Map<String,Object>> list = jdbcUtils.findMoreResult(sql, null);
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        jdbcUtils.releaseConn();
        }

        String sql = "select * from usertest where id = ?";
        List<Object> params = new ArrayList<Object>();
        params.add(3);
        try {
            UserInfo userInfo = jdbcUtils.findSimpleRefResult(sql, params, UserInfo.class);
            System.out.println(userInfo);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.releaseConn();
        }

        String sql = "select * from usertest";
        try {
            List<UserInfo> userInfos = jdbcUtils.findMoreRefResult(sql, null, UserInfo.class);
            System.out.println(userInfos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.releaseConn();
        }

    }