22---数据库编程07(preparedStatement)

 

在PreparedStatement中如果使用Date类型，必须用java.sql.Date而不是java.util.Date;
java.sql.Date继承了java.util.Date;
1、PreparedStatement的使用：
 //执行修改：
 import java.sql.DriverManager;
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.text.*;
 public class Demo{
  //定义数据库的驱动程序
  public static final String DBDRIVER="org.gjt.mm.mysql.Driver";
  //定义数据库的连接地址：
  public static final String DBURL="jdbc:mysql://localhost:3306/lid";
  //定义数据库的用户名
  public static final String DBUSER="root";
  //定义数据库的密码
  public static final String DBPASSWORD="123";
  public static void main(String args[]) throws Exception{
   Connection conn=null;
   PreparedStatement pstmt=null;//数据库操作
   ResultSet rs=null;
   //加载数据库驱动
   Class.forName(DBDRIVER);//创建一个该驱动类的Class对象；  
   String name="张三";
   String password="333";
   int age =19;
   String sex="男";
   String birthday="2001-01-01";
   java.util.Date temp=null;
   temp=new SimpleDateFormat("yyyy-MM-dd").parse(birthday);
   java.sql.Date bir=new java.sql.Date(temp.getTime());
   conn=DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD); 
   String sql="insert into user (name,password,age,sex,birthday) values(?,?,?,?,?)";
   pstmt=conn.prepareStatement(sql);//实例化PreparedStatement
   pstmt.setString(1,name);
   pstmt.setString(2,password);
   pstmt.setInt(3,age);
   pstmt.setString(4,sex);
   pstmt.setDate(5,bir);
   
   int t=pstmt.executeUpdate();
   System.out.println(t);
   pstmt.close();
   conn.close();
   }
  }

 //执行查询
 import java.sql.DriverManager;
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.text.*;
 public class Demo{
  //定义数据库的驱动程序
  public static final String DBDRIVER="org.gjt.mm.mysql.Driver";
  //定义数据库的连接地址：
  public static final String DBURL="jdbc:mysql://localhost:3306/lid";
  //定义数据库的用户名
  public static final String DBUSER="root";
  //定义数据库的密码
  public static final String DBPASSWORD="123";
  public static void main(String args[]) throws Exception{
   Connection conn=null;
   PreparedStatement pstmt=null;//数据库操作
   ResultSet rs=null;
   //加载数据库驱动
   Class.forName(DBDRIVER);//创建一个该驱动类的Class对象；  
   String key="张";
   conn=DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD); 
   String sql="select id,name,password,age,sex,birthday from user where name like ? or password like ? or sex like ?";
   pstmt=conn.prepareStatement(sql);//实例化PreparedStatement
   pstmt.setString(1,"%"+key+"%");
   pstmt.setString(2,"%"+key+"%");
   pstmt.setString(3,"%"+key+"%");
   
   rs=pstmt.executeQuery();
   while(rs.next()){
    int id=rs.getInt(1);
    String name=rs.getString(2);
    String password=rs.getString(3);
    int age=rs.getInt(4);
    String sex=rs.getString(5);
    java.util.Date d=rs.getDate(6);
    
    System.out.print("编号："+id+":");
    System.out.print("姓名："+name+":");
    System.out.print("密码："+password+":");
    System.out.print("年龄："+age+":");
    System.out.print("性别："+sex+":");
    System.out.print("生日："+d+":");
    System.out.println("------------------------------------------");
    
    }
   pstmt.close();
   conn.close();
   }
  }