欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PreparedStatement 使用 demo SQLMySQLJDBCCC++ 

程序员文章站 2022-07-14 13:34:32
...
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');

 **/

上一篇: bash

下一篇: win7 下 rails3.0安装