一阶段-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;
    }

}