设计模式(一)工厂模式-链接数据库

不使用 工厂模式的 的链接 数据库mysql

public static void main(String args[]) {

        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载MYSQL JDBC驱动程序
            // Class.forName("org.gjt.mm.mysql.Driver");//旧版本驱动，与上面的驱动一样
            System.out.println("Success loading Mysql Driver!");
        } catch (Exception e) {
            System.out.print("Error loading Mysql Driver!");
            e.printStackTrace();
        }
        try {
            Connection conn = (Connection) DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai", "root", "");
            // 连接URL为 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆数据库用户名和密码

            System.out.println("Success connect Mysql server!");
            String sql = "select * from items";// student 为你表的名称
            PreparedStatement pstm = conn.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery(sql);

            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            System.out.print("get data error!");
            e.printStackTrace();
        }

 }

　　

创建工厂模式

     数据源

public class DataSourceHolder {

    private BasicDataSource ds = new BasicDataSource();

    private DataSourceHolder() {
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai");//dbo为连接的数据库名称
        ds.setUsername("root");//账号
        ds.setPassword("");//密码
    }

    private static class SingletonHolder {
        private static DataSourceHolder instance = new DataSourceHolder();
    }

    public static DataSourceHolder getInstance() {
        return SingletonHolder.instance;
    }

    public DataSource getDataSource() {
        return ds;
    }

}

　  连接工厂

public class ConnectionFactory {
    public static Connection getConnection() {
        Connection conn = null;

        try {
            conn = DataSourceHolder.getInstance().getDataSource()
                    .getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

}

　　pojo

public class UserModel {
    private int userId;
    private String name;
    private String sex;
    private int age;
    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

}

　　DAO

public class UserJDBCDAO {
    // 增加
    public void create(UserModel user, Connection conn) {

        try {

            final String sql = "insert into tbl_user(userId,name,sex,age) values(?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            int count = 1;
            ps.setInt(count++, user.getUserId());
            ps.setString(count++, user.getName());
            ps.setString(count++, user.getSex());
            ps.setInt(count++, user.getAge());
            ps.execute();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 修改
    public void update(UserModel user,Connection conn) {

        try {
            final String sql = "update tbl_user set name=?, sex=?, age= ? where userId = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            int count = 1;

            ps.setString(count++, user.getName());
            ps.setString(count++, user.getSex());
            ps.setInt(count++, user.getAge());
            ps.setInt(count++, user.getUserId());
            ps.execute();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除
    public void delete(int userId,Connection conn) {

        try {

            final String sql = "delete from tbl_user where userId=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);

            ps.execute();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 按照主键查询一条已有的记录
    public UserModel getSingle(int userId,Connection conn) {
        UserModel user = null;

        try {

            final String sql = "select * from tbl_user where userId = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                user = this.rs2model(rs);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }

    // 将查询结果存储到userModel中
    private UserModel rs2model(ResultSet rs) throws Exception {
        UserModel user = new UserModel();
        user.setUserId(rs.getInt("userId"));
        user.setName(rs.getString("name"));
        user.setSex(rs.getString("sex"));
        user.setAge(rs.getInt("age"));
        return user;
    }

    // 查询所有记录
    public List<UserModel> getAll(Connection conn) {
        List<UserModel> list = new ArrayList<UserModel>();

        try {

            final String sql = "select * from tbl_user order by userId";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                UserModel user = this.rs2model(rs);
                list.add(user);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /*
     * 以下实现按条件查询 getByCondition方法调用generateWhere()和preparedPs()方法实现
     */

    // 实现按照组合条件查询所有符合条件的记录
    private String generateWhere(UserModel uqm) {
        StringBuffer buffer = new StringBuffer();
        // 用户选择编号作为条件
        if (uqm.getUserId() > 0) {
            buffer.append(" and userId = ?");
        }
        // 用户选择姓名作为条件
        if (uqm.getName() != null && uqm.getName().trim().length() > 0) {
            buffer.append(" and name like ?");
        }
        if (uqm.getSex() != null && uqm.getSex().trim().length() > 0) {
            buffer.append(" and sex = ?");
        }
        // 年龄的最小值
        if (uqm.getAge() > 0) {
            buffer.append(" and age >= ?");
        }
        if (uqm.getAge() < 0) {
            buffer.append(" and age<= ?");
        }
        return buffer.toString();
    }

    // 参数
    private void preparedPs(UserModel uqm, PreparedStatement ps)
            throws Exception {
        int count = 1;
        if (uqm.getUserId() > 0) {
            ps.setInt(count++, uqm.getUserId());
        }
        if (uqm.getName() != null && uqm.getName().trim().length() > 0) {
            ps.setString(count++, "%" + uqm.getName() + "%");
        }
        if (uqm.getSex() != null && uqm.getSex().trim().length() > 0) {
            ps.setString(count++, uqm.getSex());
        }
        if (uqm.getAge() > 0) {
            ps.setInt(count++, uqm.getAge());
        }
        if (uqm.getAge() > 0) {
            ps.setInt(count++, uqm.getAge());
        }
    }

    // 按条件查询，调用generateWhere(),preparedPs()实现
    public List<UserModel> getByCondition(UserModel uqm, Connection conn) {
        List<UserModel> list = new ArrayList<UserModel>();

        try {

            final String sql = "select * from tbl_user where 1=1"
                    + this.generateWhere(uqm) + " order by userId";
            PreparedStatement ps = conn.prepareStatement(sql);
            this.preparedPs(uqm, ps);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                UserModel user = this.rs2model(rs);
                list.add(user);
            }
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

}

　　关闭数据源

public class DBClose {
    public static void close(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement stmt) {
        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

　　Test

public class Test {
    public static void main(String[] args) {
        Connection conn = ConnectionFactory.getConnection();//获取数据库连接
        UserJDBCDAO dao = new UserJDBCDAO();//创建SQL处理类的实例
        dao.delete(1,conn);//执行某一个操作
        DBClose.close(conn);//关闭数据库
    }
}