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