JDBC 实战应用

1、JDBC 的使用顺序为：

         1） 注册驱动 (只做一次) 

         2） 建立连接(Connection)

       3） 创建执行SQL的语句(Statement)

          4） 处理执行结果(ResultSet)

         5） 释放资源

2、实战应用

1）封装数据库连接字符串

/**
 * JDBC配置信息
 */</span>
public class JdbcConfig {
    private String driverName;
    private String url;
    private String userName;
    private String password;
    
    public String getDriverName() {
        return driverName;
    }
    public void setDriverName(String driverName) {
        this.driverName = driverName;
    }
    public String getUrl() {
        return url;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
}

         2） 封装数据库常用操作

    public class DbUtil {
        /**
         * 取得Connection
         * @return
         */</span>
        public static Connection getConnection(){
     
            Connection conn=null;
            try {
                  //通过读取配置文件获取数据库连接字符串</span>
                JdbcConfig jdbcConfig=XmlConfigReader.getInstance().getJdbcConfig();
                  //加载驱动</span>

                Class.forName(jdbcConfig.getDriverName());
                String url=jdbcConfig.getUrl();
                String userName=jdbcConfig.getUserName();
                String password=jdbcConfig.getPassword();    
                  //建立连接</span>
                conn = (Connection) DriverManager.getConnection(url, userName, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
    }
    }

3）完成用户的添加操作

public class UserManager {
    /**
     * 添加用户
     */</span>
    public void addUser(User user){
        //创建SQL语句</span>
        String sql="insert into t_user(user_id,user_name,password,contact_tel,email,create_date)"+
                    " values(?,?,?,?,?,?)";
        
        Connection conn=null;
        PreparedStatement pstmt=null;
        try{
            //获取Connection</span>
            conn=DbUtil.getConnection();
            pstmt=conn.prepareStatement(sql);
            
            //设置参数（这种方式可防止sql注入）</span>
            pstmt.setString(1, user.getUserId());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getContactTel());
            pstmt.setString(5, user.getEmail());
            pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            
            //执行用户添加操作</span>
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            DbUtil.close(pstmt);
            DbUtil.close(conn);
        }
    }
}  

4）如果添加多个用户，则需要一点小小的变动

public class UserManager {
    /**
     * 添加用户
     */</span>
    public void addUser(List<User> userList){
        //创建SQL语句</span>
        String sql="insert into t_user(user_id,user_name,password,contact_tel,email,create_date)"+
                    " values(?,?,?,?,?,?)";
        
        Connection conn=null;
        PreparedStatement pstmt=null;
        try{
            //获取Connection</span>
            conn=DbUtil.getConnection();
            pstmt=conn.prepareStatement(sql);
            for(Iterator<User> iter=userList.iterator();iter.hasNext();){
            User user=iter.next();
            //设置参数（这种方式可防止sql注入）</span>
            pstmt.setString(1, user.getUserId());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getContactTel());
            pstmt.setString(5, user.getEmail());
            pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            pstmt.addBatch();
            }
           //执行用户添加操作</span>
            pstmt.executeBatch();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            DbUtil.close(pstmt);
            DbUtil.close(conn);
        }
    }
}