java操作数据库以及基本项目分层应用

一.jdbc的七个步骤

1.创建连接数据库的四个参数（driver,数据库url,用户名，密码）

2.加载驱动，前提要导入jar包，用Class.forName(DBdriver)，例如

Class.forName("com.mysql.jdbc.Driver");

3.进行数据库连接

Conn = DriverManager.getConnection(URL, UserName, Password);

这里的参数是第一步定义的字符串

4.创建预处理对象

PreparedStatement pstm = null;
pstm = conn.prepareStatement(sqlString);

5.执行SQL语句

rs = statement.executeQuery();

6.遍历结果集

例如

while(rs.next()){ 
String name = rs.getString(“name”) ; 
String pass = rs.getString(1) ; // 此方法比较高效 
} 

7.处理异常，关闭JDBC对象资源

一般

if (rs != null) { 
    rs.close(); 
}  
if (pstm != null) {  
    pstm.close();  
}  

if (conn != null) {  
    conn.close();  
​​​}

二.基本程序项目分层

有了jdbc的七步骤，我们可以很容易的写出一个简单的数据库查询的程序，例如：

public class Test {


    public  static  void  main(String[] args){

        Connection conn = null;

        PreparedStatement statement = null;

        ResultSet rs = null;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useSSL=true&" + 
"characterEncoding=utf-8&user=root&password=123");

            System.out.println("创建连接成功");

            String query_SQL = "select * from userinfo";

            statement = conn.prepareStatement(query_SQL);

            rs = statement.executeQuery();

            while (rs.next()){

                System.out.println(rs.getInt("uid")+"\t\t"+rs.getString("username")+"\t\t"+rs.getString("password"));

            }
        }

        catch (Exception e){
                e.printStackTrace();
        }
        finally {

            if (rs!=null){

                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }


    }
​​​​​​​
}

但是我们不可能每执行一次数据库操作都要写这么多繁琐的步骤，所以可以把数据库的相关操作抽象成一个工具类DBUtil，存放到utils文件夹下，在该工具类中实现创建数据库连接对象以及释放资源的方法。

public class DBUtil {


    private static Connection Conn; // 数据库连接对象

    // 数据库连接地址
    private static String URL = "jdbc:mysql://localhost:3306/user?characterEncoding=utf8";

    // 数据库的用户名
    private static String UserName = "root";

    // 数据库的密码
    private static String Password = "123";

    public static Connection getConnection() {

        try {
            Class.forName("com.mysql.jdbc.Driver"); 
            System.out.println("加载驱动成功!!!");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
​​​​​
        try {
            //通过DriverManager类的getConenction方法指定三个参数,连接数据库
​​​​​
            Conn = DriverManager.getConnection(URL, UserName, Password);
            System.out.println("连接数据库成功!!!");
            return Conn;


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

    }


​​​​​​​
    public void closeConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws Exception {
        if (rs != null) {
            rs.close();
        }

        if (pstm != null) {
            pstm.close();
        }

        if (conn != null) {
            conn.close();
        }

    }
​​​​​​​

}

接下来我们按照mvc的结构将这个简单的应用分层，首项要建立model文件夹，里面存放实体模型，这里用user举例

public class User {
    private int id;
    private String username;
    private String password;

    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;
    }


    public User() {
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }
}

有了实体，就要想对实体的操作了，这里拿用户登录举例，为了方便，这里先编写view视图层，毕竟有需求才有业务逻辑。简单写一个登录

public class LoginUI {

    public static void main(String[] args){

        Scanner inp = new Scanner(System.in);
        while(true){
        System.out.println("请输入用户名：");
        String username = inp.next();
        System.out.println("请输入密码：");
        String password = inp.next();
        boolean result = new LoginServiceImpl().doLogin(new User(username,password));
        System.out.println(result);
        }
    }
}

代码中有LoginServiceImpl()，这里相当于在controller控制层，这一层存放在文件夹service，LoginServiceImpl实现的是LoginService接口，在LoginServiceImpl中去调用model层去执行对数据库处理的操作

public class LoginServiceImpl implements LoginService {
    @Override
    public boolean doLogin(User loginUser) {
        return new CUser().queryUser(loginUser);
    }
}

这层中使用了CUser().queryUser(loginUser);
这些对象与方法存放在dao文件夹，dao就像是一个orm（对象关系映射）从dao层看，对数据库的操作就像是对类和对象的操作一样。
同样，dao也是接口

public interface UserDao {
    void insertUser(User newUser) throws Exception;  // 添加用户的方法
    void deleteUser(int id) throws Exception;  // 删除用户的方法
    void updateUser(int id,User modUser) throws Exception;  // 更新用户的方法
    void selectByID(int id) throws Exception;   // 根据id查询用户
    boolean queryUser(User queryUser) throws Exception;   // 根据用户名密码查询用户
}

我们在CDao中实现这些接口,下面是CDao文件夹下的UserDao类

public class CUser implements UserDao{
    DBHelp dh = new DBHelp();
    private Connection conn;

    @Override
    public void insertUser(User newUser) throws Exception {
        // 将用户信息添加到后台数据库表中
        Connection conn = dh.getConnection();
        PreparedStatement pstm = null;
        String sql_insert = "insert into userinfo(username,password) values(?,?)";
        pstm = conn.prepareStatement(sql_insert);

        pstm.setString(1, newUser.getUsername());
        pstm.setString(2, newUser.getPassword());

        int row = pstm.executeUpdate();
        System.out.println("新增用户成功" + row + "行受到影响");
        dh.closeConn(null, pstm, conn);
    }

    @Override
    public void deleteUser(int id) throws Exception{

        Connection conn = dh.getConnection();
        PreparedStatement pstm = null;
        String sql_delete = "delete from userinfo where uid=?";
        pstm = conn.prepareStatement(sql_delete);

        pstm.setInt(1, id);

        int row = pstm.executeUpdate();
        System.out.println("删除用户成功" + row + "行受到影响");
        dh.closeConn(null, pstm, conn);

    }

    @Override
    public void updateUser(int id,User modUser) throws Exception{
        Connection conn = dh.getConnection();
        PreparedStatement pstm = null;
        String sql_update = "update userinfo set username=? password=? where uid=?";
        pstm = conn.prepareStatement(sql_update);

        pstm.setInt(1, id);
        pstm.setString(2, modUser.getUsername());
        pstm.setString(3, modUser.getPassword());

        int row = pstm.executeUpdate();
        System.out.println("更改用户成功" + row + "行受到影响");
        dh.closeConn(null, pstm, conn);

    }

    @Override
    public void selectByID(int id) throws Exception{
        Connection conn = dh.getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql_selectById = "select * from userinfo where uid=?";
        pstm = conn.prepareStatement(sql_selectById);

        pstm.setInt(1,id);

        rs = pstm.executeQuery();
        System.out.println("查询成功,信息如下:");
        System.out.println("用户编号\t\t用户名称\t\t用户密码");
        while (rs.next()) {
            System.out.println(rs.getInt("uid") + "\t\t\t" + rs.getString("username") + "\t\t" + rs.getString("password"));
        }
        }

    @Override
    public boolean queryUser(User queryUser) {
        Connection conn = dh.getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String query_SQL = "select count(*) as num from userinfo where username=? and password=?";
        try {
            pstm = conn.prepareStatement(query_SQL);
            pstm.setString(1,queryUser.getUsername());
            pstm.setString(2,queryUser.getPassword());

            rs = pstm.executeQuery();
            while(rs.next()){
                int num = rs.getInt("num");
                if (num>0){
                    return true;
                }
                else {
                    return false;
                }

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

        return false;

    }

}

UserDao也就是LoginService中使用的函数了。
最后看一下完整的目录结构