PreparedStatement 使用 demo

package cc.java.sql.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
/**
* 代码参考：http://topic.csdn.net/u/20080519/16/b3aca5ab-e29c-4c8a-a066-27d7607ee072.html
* 作为使用PreparedStatement的DEMO，简单的登陆与创建。
* 数据库脚本见TEST_PreparedStatement.sql
**/
public class Login {
   private static Connection conn ;
   private static ResultSet res ;
   private static java.sql.PreparedStatement prepar ; 
   private static List<UserBean> userList;
   public Login(){
       try   {
           Class.forName("com.mysql.jdbc.Driver");
           conn = DriverManager
           .getConnection(
                   "jdbc:mysql://localhost:3306/test","root","111111");
           System.out.println("连接成功");
       }catch (SQLException ex){
           System.out.println(ex.getMessage() + "SQL错误");
       }catch (ClassNotFoundException ex){
           System.out.println(ex.getMessage() + "错误");
       } 
   }
   public static Connection getConn(){
       try   {
           Class.forName("com.mysql.jdbc.Driver");
           conn = DriverManager
           .getConnection(
                   "jdbc:mysql://localhost:3306/test","root","111111");
           System.out.println("连接成功");
       }catch (SQLException ex){
           System.out.println(ex.getMessage() + "SQL错误");
       }catch (ClassNotFoundException ex){
           System.out.println(ex.getMessage() + "错误");
       } 
       return conn;
   }
   public void close(){ 
       try   {
           if (res != null){
               res.close();
           }
           if (prepar != null){
               prepar.close();
           }
           if (conn != null){
               conn.close();
           }
       }catch (SQLException ex){
           ex.printStackTrace();
       }
       System.out.println("关闭成功");
   }
   public boolean checkUserLogin(String name,String password)
   {
       boolean result = false;
       try   {
           String sql =
               "select count(*) from test_preparedstatement where username=? and password=?";
           prepar = conn.prepareStatement(sql);
           prepar.setString(1, name);
           prepar.setString(2, password);
           res = prepar.executeQuery();
           if (res.next())   {
               if (res.getInt(1) > 0){
                   result = true;
               }
           }
       }catch (Exception e)   {
           e.printStackTrace();
       }finally{
           close();
       }
       return result;
   }
   public void addUser(UserBean person){
       try   {
           String sql="insert into test_preparedstatement (username,password,mail,registtime) values(?,?,?,?)";
           prepar=conn.prepareStatement(sql);
           prepar.setString(1,person.getUsername());
           prepar.setString(2,person.getPassword());
           prepar.setString(3,person.getMail());
           prepar.setTimestamp(4,person.getRegistTime());
           prepar.executeUpdate();
       }catch(Exception e){
           e.printStackTrace();
       }finally{
           close();
       }
   }
   /**用于批量添加用戶**/
   public void addUser(List<UserBean> user){
       try   {
       String sql="insert into test_preparedstatement (username,password,mail,registtime) values(?,?,?,?)";
       prepar=conn.prepareStatement(sql);
       for (Iterator iterator = user.iterator(); iterator.hasNext();) {
           UserBean userBean = (UserBean) iterator.next();
           prepar.setString(1,userBean.getUsername());
           prepar.setString(2,userBean.getPassword());
           prepar.setString(3,userBean.getMail());
           prepar.setTimestamp(4,userBean.getRegistTime());
           prepar.addBatch();
       }
       prepar.executeBatch();
       }catch(Exception e){
           e.printStackTrace();
       }finally{
           close();
       }
   }
   /**用于批量修改**/
   public void updatebatch(List<UserBean> user){
       try   {
           String sql="update test_preparedstatement set password=? where user=?";
           prepar=conn.prepareStatement(sql);
           for (Iterator iterator = user.iterator(); iterator.hasNext();) {
               UserBean userBean = (UserBean) iterator.next();
               prepar.setString(1,userBean.getUsername());
               prepar.setString(2,userBean.getPassword());
               prepar.addBatch();
           }
           prepar.executeBatch();
       }catch(Exception e){
           e.printStackTrace();
       }finally{
           close();
       }
   }
   public static void main(String[] args) {
       // TODO Auto-generated method stub
       Login login=new Login();
       userList=login.initdb();
       login.addUser(userList);
       
   
   }
   /**建立表后使用此方法填充数据**/
   public List<UserBean> initdb(){
       List<UserBean> userLists=new ArrayList<UserBean>();
       for (int i = 0; i < 10; i++) {
           String name_pass="test"+i;
           userLists.add(new UserBean(name_pass,name_pass,"name_pass",getTime()));
       }
       return userLists;
   }
   /**得到当前时间**/
   public static Timestamp getTime(){
       Calendar c = Calendar.getInstance();
//       c.set(2009, 2, 12, 0, 9, 22);// test
       return new Timestamp(c.getTimeInMillis());
   }   
}
class UserBean{

   private String username;
   private String password;
   private String mail;
   private Timestamp registTime;
   
   public UserBean(String username, String password, String mail,
           Timestamp registTime) {
       super();
       this.username = username;
       this.password = password;
       this.mail = mail;
       this.registTime = registTime;
   }
   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 String getMail() {
       return mail;
   }
   public void setMail(String mail) {
       this.mail = mail;
   }
   public Timestamp getRegistTime() {
       return registTime;
   }
   public void setRegistTime(Timestamp registTime) {
       this.registTime = registTime;
   } 
}

/**

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test_preparedstatement
-- ----------------------------
CREATE TABLE `test_preparedstatement` (
`id` int(10) NOT NULL auto_increment,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`mail` varchar(20) NOT NULL,
`registtime` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `test_preparedstatement` VALUES ('1', 'test0', 'test0', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('2', 'test1', 'test1', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('3', 'test2', 'test2', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('4', 'test3', 'test3', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('5', 'test4', 'test4', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('6', 'test5', 'test5', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('7', 'test6', 'test6', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('8', 'test7', 'test7', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('9', 'test8', 'test8', 'name_pass', '2009-03-30 10:59:15');
INSERT INTO `test_preparedstatement` VALUES ('10', 'test9', 'test9', 'name_pass', '2009-03-30 10:59:15');

**/