JDBC进行数据库增删查改

前情回顾

用预编译的方法操作数据库：

PreparedStatement的使用：

1.使用Connection的prepareStatement(String sql)：即创建它时就让它与一条SQL模板绑定；

String sql = "select * from user where name=? and password=?";
PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);

2.调用PreparedStatement的setXXX()系列方法为sql命令设置值；

pstmt.setString(1, username);
pstmt.setString(2, password);  

3.调用executeUpdate()或executeQuery()方法，但要注意，调用没有参数的方法；

executeUpdate()：insert,delete,update

executeQuery(): select

rs = pstmt.executeQuery();//执行查询

setXXX()方法

prepareStatement中跟setString类似的方法有：

             pstmt.setByte(parameterIndex, x);
             pstmt.setDate(parameterIndex, x);
             pstmt.setDouble(parameterIndex, x);
             pstmt.setFloat(parameterIndex, x);
             pstmt.setInt(parameterIndex, x);
             pstmt.setLong(parameterIndex, x);
             pstmt.setString(parameterIndex, x);

可以通过他们为SQL查询中不同数据类型的字段赋值，

parameterIndex指代模板中位置的序号，x指代要填入的值。

JDBC功能操作

建立连接

代码：

private static Connection getConn() {
    	// JDBC 驱动名及数据库 URL
       final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
       final String DB_URL = "jdbc:mysql://localhost:3306/cloud";
     
        // 数据库的用户名与密码，需要根据自己的设置
       final String USER = "root";
       final String PASS = "000000";
       
    	Connection conn = null;       
        try {
            Class.forName(JDBC_DRIVER); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("MySQL连接成功！");
        } catch (ClassNotFoundException e) {
        	System.out.println(e.getMessage());
        } catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
        return conn;
}

插入数据

代码：

private  int insert(User user) {
        Connection conn =  this.con;
        int i = 0;
        String sql = "insert into user (name,password) values(?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getPassword());  //pstmt.setInt(2, user.getAge()); pstmt.setDate  
                        
            i = pstmt.executeUpdate();
            if(i!=0){    	    System.out.println(user.getName()+"插入成功!");
	    }
            pstmt.close();
            
        } catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
        return i;
    }

删除记录

//删除记录
    public int delete(User user) {  
    	Connection conn = this.con;
        int i = 0;
		String sql = "DELETE FROM user WHERE name = ?"; 
		
    	try {   		
          
    		PreparedStatement pstmt = conn.prepareStatement(sql);  
    		pstmt.setString(1, user.getName());            
    		i = pstmt.executeUpdate();
                if(i!=0){    	    System.out.println(user.getName()+"删除成功!");
	    }
    		pstmt.close();
            
    	}catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
       return i;
    }

查询记录

	
    public void query(String s){
		try { 
    		ResultSet rs = null;   //建立一个空的结果集

            //1.创建PreparedStatement 对象，让它与一条SQL模板绑定；
    		String sql = "select * from user where name like ?";
    		PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
    		

            //使用2. setXXX()系列方法为sql命令设置值
            pstmt.setString(1, "%"+s+"%");//                

            //3.执行查询 
            rs = pstmt.executeQuery();//执行查询
            int col = rs.getMetaData().getColumnCount();//获取字段数
            
            
            //4. 打印数据表表头
            System.out.println("============================");
            for (int i = 1; i <= col; i++) {
            	String col_name = rs.getMetaData().getColumnName(i);
            	System.out.print(col_name+"\t");
            	
            }
            System.out.println("");
            
           while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {//此行仅仅是为了显示更好看，可以删去
                        System.out.print("\t");
                    }
                 }
                System.out.println("");
            }
            System.out.println("============================");
    		pstmt.close();
    		
            
    	 }catch (SQLException e) {//捕捉、处理异常
             	System.out.println(e.getMessage());
        }
    	
		
	} 

修改记录

//改记录
   private int update(String name,String new_pwd) {
    	 
         int i = 0;
         String sql = "UPDATE user SET  password=?"  
                 + " WHERE name=?";  
         PreparedStatement pstmt;
         try {
             pstmt = (PreparedStatement) conn.prepareStatement(sql);
             pstmt.setString(1, new_pwd);
             pstmt.setString(2, name);            
             i = pstmt.executeUpdate();
             if(i!=0){ 
            	 System.out.println(name+"修改成功!");
	         }
             pstmt.close();
             
         } catch (SQLException e) {
         	System.out.println(e.getMessage());
         }
         return i;          
   
    }

测试主类

    public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
		User u1=new User("zhangqi","123");
		User u2=new User("zhangba","123");
		User u3=new User("zhangjiu","123");
        TestSQL t =new TestSQL();
        t.getConn();//建立mysql连接
        t.insert(u1);t.insert(u2);t.insert(u3);//插入数据
        t.delete(u1);//删除数据
        t.query("zhang");//模糊查询
        t.update("zhangba", "456");
        t.query("zhangba");//精确匹配
        t.query("");//显示全部
        
	}

完整代码

package task9;
import java.sql.*;



public class TestSQL {
	 final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
     final String DB_URL = "jdbc:mysql://10.0.0.30:3306/cloud?characterEncoding=utf8";
   
      // 数据库的用户名与密码，需要根据自己的设置
     final String USER = "root";
     final String PASS = "000000";
     
  	Connection conn = null; 
  	
    private  Connection getConn() throws Exception{
    	// JDBC 驱动名及数据库 URL
            
         try {
             Class.forName(JDBC_DRIVER); //classLoader,加载对应驱动
             conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
             System.out.println("MySQL连接成功！");
         } catch (ClassNotFoundException e) {
         	System.out.println(e.getMessage());
         } catch (SQLException e) {
         	System.out.println(e.getMessage());
         }
         return conn;
    }
    public static void verify(String username, String password)throws Exception {
    	try { 
    		Connection con = getConn();
    		Statement stmt = null;  
    		ResultSet rs = null;       
    		stmt = con.createStatement();
    		String sql = "SELECT * FROM user WHERE " + "name='" + username + 
                   "' and password='" + password + "'";
    		rs = stmt.executeQuery(sql);
    		if(rs.next()) { 
    			System.out.println("欢迎" + rs.getString("name"));
    		} else {  
    			System.out.println("用户名或密码错误！"); 
    		}
                con.close();
    	 }	catch (SQLException e) {
             	System.out.println(e.getMessage());
         }
    	
    }
    private  int insert(User user) {
        
        int i = 0;
        String sql = "insert into user (name,password) values(?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getPwd());  //pstmt.setInt(2, user.getAge()); pstmt.setDate  
                        
            i = pstmt.executeUpdate();
            if(i!=0){    	    System.out.println(user.getName()+"插入成功!");
	    }
            pstmt.close();
            
        } catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
        return i;
    }
  //删除记录
    public int delete(User user) {  
    	
        int i = 0;
		String sql = "DELETE FROM user WHERE name = ?"; 
		
    	try {  
    		
    		PreparedStatement pstmt = conn.prepareStatement(sql);  
    		pstmt.setString(1, user.getName());            
    		i = pstmt.executeUpdate();
            if(i!=0){ 
            	System.out.println(user.getName()+"删除成功!");
	        }
    		pstmt.close();
            
    	}catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
        return i;
    }
    public void query(String s){
		try { 
    		ResultSet rs = null;   //建立一个空的结果集

            //1.创建PreparedStatement 对象，让它与一条SQL模板绑定；
    		String sql = "select * from user where name like ?";
    		PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
    		

            //使用2. setXXX()系列方法为sql命令设置值
            pstmt.setString(1, "%"+s+"%");//                

            //3.执行查询 
            rs = pstmt.executeQuery();//执行查询
            int col = rs.getMetaData().getColumnCount();//获取字段数
            
            
            //4. 打印数据表表头
            System.out.println("============================");
            for (int i = 1; i <= col; i++) {
            	String col_name = rs.getMetaData().getColumnName(i);
            	System.out.print(col_name+"\t");
            	
            }
            System.out.println("");
            
           while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {//此行仅仅是为了显示更好看，可以删去
                        System.out.print("\t");
                    }
                 }
                System.out.println("");
            }
            System.out.println("============================");
    		pstmt.close();
    		
            
    	 }catch (SQLException e) {//捕捉、处理异常
             	System.out.println(e.getMessage());
        }
    	
		
	}  
    
    private int update(String name,String new_pwd) {
    	 
         int i = 0;
         String sql = "UPDATE user SET  password=?"  
                 + " WHERE name=?";  
         PreparedStatement pstmt;
         try {
             pstmt = (PreparedStatement) conn.prepareStatement(sql);
             pstmt.setString(1, new_pwd);
             pstmt.setString(2, name);            
             i = pstmt.executeUpdate();
             if(i!=0){ 
            	 System.out.println(name+"修改成功!");
	         }
             pstmt.close();
             
         } catch (SQLException e) {
         	System.out.println(e.getMessage());
         }
         return i;          
   
    }
    public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
		User u1=new User("zhangqi","123");
		User u2=new User("zhangba","123");
		User u3=new User("zhangjiu","123");
        TestSQL t =new TestSQL();
        t.getConn();//建立mysql连接
        t.insert(u1);t.insert(u2);t.insert(u3);//插入数据
        t.delete(u1);//删除数据
        t.query("zhang");//模糊查询
        t.update("zhangba", "456");
        t.query("zhangba");//精确匹配
        t.query("");//显示全部
        
	}

}