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');

 **/