一阶段-nineteenthWork-数据库操作

/*
在任意数据库中建立表 userinfo 包含字段 Username varchar(10), password varchar(10);
1. 编写java类实现使用Statement类实现用户登录操作，控制台输入用户名和密码
2. 编写java类实现使用PreparedStatement 实现用户登录操作，控制台输入用户名和密码 登录成功之后依次实现：
a. 增加用户 *控制台输入用户名和密码
b. 修改用户密码 *控制台输入用户名，修改指定用户名的密码
c. 删除用户 *控制台输入用户名，删除指定用户名的密码
d. 查询所有用户信息
*/

package nineteenthWork;

public class RunMain
{
public static void main(String[] args)
{
DriverStep driver = new DriverStep();
driver.run();
}
}
=====================================

package nineteenthWork;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

/**
*
* @author Administrator
*/
public class DriverStep
{
private String yesNo="";
public void run()
{
UserVO vo = new UserVO();
LoginFrame log = new LoginFrame();
UserinfoDao jddao = new UserinfoDao();
System.out.println("-------------请先登录!-----------");
log.Login(vo);//用"张三","123"登录成功
if(!jddao.resultLog(vo))
{
System.exit(0);
}
System.out.println("是否要增加新用户,请输入'是'或者'否': ");
BufferedReader buff = new BufferedReader(new InputStreamReader(System.in));
try {
yesNo = buff.readLine();
} catch (IOException ex) {
ex.printStackTrace();
}
if(yesNo.equals("是"))
{
log.inintUser(vo);//增加用户
}
System.out.println("是否要执行修改某用户密码的操作,请输入'是'或者'否': ");
try {
yesNo = buff.readLine();
} catch (IOException ex) {
ex.printStackTrace();
}
if(yesNo.equals("是"))
{
log.inintUpdatePasswork(vo);//修改密码
}
System.out.println("是否要执行删除某用户密码的操作,请输入'是'或者'否': ");
try {
yesNo = buff.readLine();
} catch (IOException ex) {
ex.printStackTrace();
}
if(yesNo.equals("是"))
{
log.inintDeletePassword(vo);//删除密码
}
System.out.println("是否要执行查询所有用户信息的操作,请输入'是'或者'否': ");
try {
yesNo = buff.readLine();
} catch (IOException ex) {
ex.printStackTrace();
}
if(yesNo.equals("是"))
{
log.resultSelectAll();//查询所有用户信息
}
System.out.println("程序执行完毕");

}
}
==========================================
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package nineteenthWork;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author Administrator
*/
public class Factory
{
Connection conn = null;
public Connection getConn()
{
try {
String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String URL = "jdbc:sqlserver://John:1433;databaseName=students;user=john;password=123";
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL);
} catch (ClassNotFoundException ex) {
Logger.getLogger(Factory.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
ex.printStackTrace();
}
return conn;
}
public void close()
{
if(conn != null)
{
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
==================================


package nineteenthWork;
import java.sql.*;

public class JdbcStatementDAO
{

public boolean logData(UserVO vo)
{
boolean activity = false;
try {
Factory factory = new Factory();
Connection conn = factory.getConn();
Statement stmt = conn.createStatement();
// String sql = "select * from userinfo where username='"+vo.getUsername()+"' and password ='"+vo.getPassword()+"'";
String username = vo.getUsername();
String password = vo.getPassword();
String sql = "select * from userinfo where username='"+username+"' and password ='"+password+"'";
ResultSet rs = stmt.executeQuery(sql);
activity = rs.next();
} catch (Exception ex) {
System.out.println(ex.toString());
}
return activity;
}
}
==================================

package nineteenthWork;

import java.io.BufferedReader;
import java.io.InputStreamReader;


public class LoginFrame
{
public void Login(UserVO vo)//验证登录结果
{
// JdbcStatementDAO info = new JdbcStatementDAO();//这是Statement的实现方法
UserinfoDao info = new UserinfoDao();//这是PreParedStatement的实现方法
BufferedReader buff = new BufferedReader(new InputStreamReader(System.in));

try{
System.out.println("请输入登录名: ");
vo.setUsername(buff.readLine()) ;
System.out.println("请输入密码: ");
vo.setPassword(buff.readLine());
if(info.resultLog(vo))
{
System.out.println("登录成功!");
}
else{
System.out.println("登录失败!");
}
}catch(Exception e)
{
System.out.println();
}
}
public void inintUser(UserVO vo)//新增用户信息
{
UserinfoDao info = new UserinfoDao();//这是PreParedStatement的实现方法
BufferedReader buff = new BufferedReader(new InputStreamReader(System.in));
try{
System.out.println("请输入新登录名: ");
vo.setUsername(buff.readLine()) ;
System.out.println("请输入密码: ");
vo.setPassword(buff.readLine());
if(info.insertUser(vo))
{
System.out.println("注册成功!");
}
else{
System.out.println("注册失败!");
}
}catch(Exception e)
{
System.out.println();
}
}
public void inintUpdatePasswork(UserVO vo)//修改某用户的密码
{
UserinfoDao info = new UserinfoDao();//这是PreParedStatement的实现方法
BufferedReader buff = new BufferedReader(new InputStreamReader(System.in));
try{
System.out.println("请输入要修改的用户名: ");
vo.setUsername(buff.readLine()) ;
System.out.println("请输入新密码: ");
vo.setPassword(buff.readLine());
if(info.updatePassword(vo))
{
System.out.println("修改成功!");
}
else{
System.out.println("修改失败!");
}
}catch(Exception e)
{
System.out.println();
}
}
public void inintDeletePassword(UserVO vo)//删除某用户的密码
{
UserinfoDao info = new UserinfoDao();//这是PreParedStatement的实现方法
BufferedReader buff = new BufferedReader(new InputStreamReader(System.in));
try{
System.out.println("请输入你想要删除的用户的用户名: ");
vo.setUsername(buff.readLine()) ;
// System.out.println("请输入该用户的密码: ");
// vo.setPassword(buff.readLine());
if(info.deletePassword(vo))
{
System.out.println("删除密码成功!");
}
else{
System.out.println("删除密码失败!");
}
}catch(Exception e)
{
System.out.println();
}
}
public void resultSelectAll()//查询所有用户的信息
{
UserinfoDao info = new UserinfoDao();//这是PreParedStatement的实现方法
info.selectAll();
}
}
=================================

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package nineteenthWork;

/**
*
* @author Administrator
*/
public class UserVO
{
private String username;
private String password;

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}


}
===========================================
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package nineteenthWork;
import java.sql.*;
/**
*
* @author Administrator
*/
public class UserinfoDao
{

public boolean resultLog(UserVO vo)//返回登录结果
{
boolean activity = false;
Factory factory = new Factory();
try{
Connection conn = factory.getConn();//获取连接数据库
String sql = "select * from userinfo where username=? and password =?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, vo.getUsername());
ps.setString(2, vo.getPassword());
ResultSet rs = ps.executeQuery();
activity = rs.next();
}
catch(Exception e)
{
System.out.println(e.toString());
}finally{
factory.close();//关闭数据连接
}
return activity;
}
public boolean insertUser(UserVO vo)//增加新的用户信息
{
Factory factory = new Factory();
boolean activity = false;
try{

Connection conn = factory.getConn();
// String username = vo.getUsername();
// String password = vo.getPassword();
String sql = "insert into userinfo (username,password)values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, vo.getUsername());
ps.setString(2, vo.getPassword());
int i = ps.executeUpdate();
System.out.println("影响行数:"+i+"行");
if(i>0)
{
activity = true;
}
}
catch(Exception e)
{
System.out.println(e.toString());
}finally{
factory.close();
}
return activity;
}
public boolean updatePassword(UserVO vo)//修改用户的密码
{
Factory factory = new Factory();
boolean activity = false;
try{
Connection conn = factory.getConn();
String sql = "update userinfo set password=? where username=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, vo.getPassword());
ps.setString(2, vo.getUsername());
int i = ps.executeUpdate();
System.out.println("影响行数:"+i+"行");
if(i>0)
{
activity = true;
}
}
catch(Exception e)
{
System.out.println(e.toString());
}finally{
factory.close();
}
return activity;
}
public boolean deletePassword(UserVO vo)//删除某用户的密码
{
boolean activity = false;
Factory factory = new Factory();
try{
Connection conn = factory.getConn();
String sql = "update userinfo set password=null where username=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, vo.getUsername());
int i = ps.executeUpdate();
System.out.println("影响行数:"+i+"行");
if(i>0)
{
activity = true;
}
}
catch(Exception e)
{
System.out.println(e.toString());
}finally{
factory.close();
}
return activity;
}
public boolean selectAll()//查询表userinfo中所有用户的信息
{
boolean activity = false;
Factory factory = new Factory();
try {
Connection conn = factory.getConn();
String sql = "select * from userinfo";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("username")+"\t"+rs.getString("password"));
activity = true;
}
} catch (SQLException ex) {
ex.printStackTrace();
}finally{
factory.close();
}
return activity;
}

}