java连接mysql数据库 三 实现增删改查操作

 

同以前一样，先写一个数据库打开和关闭操作类

 

public class DBConnection {

    String driver = "com.mysql.jdbc.Driver";
    String url= "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "123456";
    
    public Connection conn;

    public DBConnection() {

        try {
            Class.forName(driver);// 加载驱动程序
            conn = (Connection) DriverManager.getConnection(url, user, password);// 连续数据库
            
            if(!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!"); 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void close() {
        try {
            this.conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

public class JDBCTest {

    public static void main(String[] args){
        //add(uname, uemail, upwd);
        //update("李诗诗","lishishi@com","666");
        //show();
        del("王小五");
    }
    //插入操作
    public static int add(String uname,String uemail,String upwd) {
        int i=0;
        String sql="insert into employee (name,email,pwd) values (?,?,?)";
        DBConnection db = new DBConnection();
        try {        
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.setString(1, uname);
            preStmt.setString(2, uemail);
            preStmt.setString(3, upwd);
            preStmt.executeUpdate();
            //Statement statement = (Statement) db.conn.createStatement();
            //statement.executeUpdate(sql);
            
            preStmt.close();
            db.close();//关闭连接 
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;//返回影响的行数，1为执行成功
    }
    //查找操作
    public static void show(){
         String sql ="select * from employee";
         DBConnection db = new DBConnection();
         
         System.out.println("-----------------");
         System.out.println("姓名" +"\t"+ "邮箱" +"\t"+ "日期");
         System.out.println("-----------------");
         
         try {
            Statement stmt = (Statement) db.conn.createStatement();
            ResultSet rs = (ResultSet) stmt.executeQuery(sql);
            while(rs.next()){
                String uname = rs.getString("name");
                String uemail = rs.getString("email");
                String uhiredate = rs.getString("hiredate");
                //可以将查找到的值写入类，然后返回相应的对象 
                //这里 先用输出的端口显示一下
                System.out.println(uname +"\t"+ uemail +"\t"+ uhiredate);
            }
            rs.close();
            db.close();//关闭连接 
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }
    //更新操作
    public static int update(String uname,String uemail,String upwd) {
        int i =0;
        String sql="update employee set email=?,pwd=? where name=?";
        DBConnection db = new DBConnection();
        
        try {
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.setString(1, uemail);
            preStmt.setString(2, upwd);
            preStmt.setString(3, uname);
            preStmt.executeUpdate();
            
            preStmt.close();
            db.close();//关闭连接 
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;//返回影响的行数，1为执行成功
    }
    //删除操作
    public static int del(String uname) {
        int i=0;
        String sql="delete from employee where name=?";
        DBConnection db = new DBConnection();
        try {    
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.setString(1, uname);
            preStmt.executeUpdate();
            
            preStmt.close();
            db.close();//关闭连接 
        } catch (SQLException e){
            e.printStackTrace();
        }
        return i;//返回影响的行数，1为执行成功
    }
}