JDBC改善Java编程，增删改查

JDBC规范：

在写SQL语句时，常常客户端书写会不小心用到关键字，造成SQL语句发生变化，Java中提供preparedStatement，会先编译SQL语句

preparedStatement：预编译对象， 是Statement对象的子类。

特点：

性能要高

会把sql语句先编译

sql语句中的参数会发生变化，过滤掉用户输入的关键字。

下面我们来写用户登录的功能

1，首先，创建一个实体类，set，get方法，属性要跟数据库字段名一致（User类）

public class User {
    private int id;
    private String name;
    private String password;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }

}

2，JDBCUtils工具类（JDBCUtils类）

导的包都是java.sql下的

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtils {
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static{
        ResourceBundle rb=ResourceBundle.getBundle("dbinfo");
        driverClass=rb.getString("driverClass");
        url=rb.getString("url");
        username=rb.getString("username");
        password=rb.getString("password");
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception{

        return DriverManager.getConnection(url, username, password);
    }
    public static void closeall(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        rs=null;
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        stmt=null;
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        conn=null;
    }
}

3，配置文件.xml (dbinfo.properties)

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
username=lml
password=123

4，写一个类，实现增删改查（Login类）

public class Login {
    Connection conn=null;
    PreparedStatement stmt=null;
    ResultSet rs=null;
    User u=null;
    //查找
    public User findlogin(String name,String pwd){
        try {
            conn=DBUtils.getConnection();
            String sql="select * from users where name=? and password=?";
            stmt=conn.prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, pwd);
            rs=stmt.executeQuery();

            if(rs.next()){
                u=new User();
                u.setId(rs.getInt(1));
                u.setName(rs.getString(2));
                u.setPassword(rs.getString(3));

            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtils.closeall(rs, stmt, conn);
        }

        return u;
    }
    //更新
    public void update(){
        try {
            conn=DBUtils.getConnection();
            stmt=conn.prepareStatement("UPDATE users SET id=?,name=?,password=? WHERE id=1");
            stmt.setInt(1, 1);
            stmt.setString(2, "nn");
            stmt.setString(3, "456");
            int i=stmt.executeUpdate();
            if(i>0){
                System.out.println("success");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtils.closeall(null, stmt, conn);
        }

    }
    //删除
    public void delete(){
        try {
            conn=DBUtils.getConnection();
            stmt=conn.prepareStatement("DELETE FROM users WHERE id=?");
            stmt.setInt(1, 1);

            int i=stmt.executeUpdate();
            if(i>0){
                System.out.println("success");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtils.closeall(null, stmt, conn);
        }

    }
    //添加
    public void insert(){
        try {
            conn=DBUtils.getConnection();
            stmt=conn.prepareStatement("INSERT INTO users VALUES(?,?,?)");
            stmt.setInt(1, 1);
            stmt.setString(2, "lily");
            stmt.setString(3, "123");
            int i=stmt.executeUpdate();
            if(i>0){
                System.out.println("success");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtils.closeall(null, stmt, conn);
        }

    }
}

4，实现上面类（Login类）里面的方法（Test_login类）

public class Test_login {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        /*Scanner sc=new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name=sc.nextLine();
        System.out.println("请输入密码：");
        String pwd=sc.nextLine();

        Login login=new Login();
        User user=login.findlogin(name, pwd);
        if(user!=null){
            System.out.println("登录成功，欢迎你"+user.getName());
        }else{
            System.out.println("用户名或密码错误");
        }*/
        Login login=new Login();
        //login.update();
        //login.delete();
        login.insert();
    }

}

思维导图