一阶段-twentithWork-数据库操作

/*
在任意数据库中建立表 userinfo 包含字段Username varchar(10), password varchar(10);

1，建立数据库连接类 DBUtil ，负责连接数据库.关键方法：
public  Connection getConn()
具体要求：获得数据库连接，要求处理异常
2，建立数据库操作类，使用数据库连接类获得数据库连接。进行数据库的增删改查操作
关键方法：
A  public String addUser(String name,String password) 添加用户，成功返回“添加成功”
失败返回添加失败
B  public String EditPassword(String name,String passwod)修改指定用户的密码，成功返回“修改成功”。失败返回“修改失败”，若未修改任何数据返回“无此用户”
C  public String delUser(String name) 删除指定用户  成功返回“删除成功”。失败返回“删除失败”，若未修改任何数据返回“无此用户”
*注：若出现异常则是操作失败的情况，以上方法是用预编译操作

D  public void getResult(String username,String password)
具体要求：以用户名和密码作为参数传入，若传入的参数不为空，则作为查询的条件使用，查询用户的信息
3，编写测试类：
增加一条用户信息
删除一条用户信息
修改一条用户信息
查询用户信息
*/

package twentithWork.TestPack;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import twentithWork.dao.DBContron;

/**
*
* @author Administrator
*/
public class Test
{
    public void run()
    {
        String name = "";
        String password = "";
        DBContron contron = new DBContron();
        InputStreamReader in = new InputStreamReader(System.in);
        BufferedReader buff = new BufferedReader(in);
        System.out.println("--数据插入--");
        System.out.println("请输入用户名：");
        try{
            name = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        System.out.println("请输入密码：");
                try{
            password = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        System.out.println(contron.addUser(name, password));
        System.out.println("执行成功！");
        //////////////
        System.out.println("--数据修改--");
        System.out.println("请输入用户名：");
        try{
            name = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        System.out.println("请输入新密码：");
                try{
            password = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        System.out.println(contron.EditPassword(name, password));
        System.out.println("执行成功！");
        //////////////
        System.out.println("--数据删除--");
        System.out.println("请输入用户名：");
        try{
            name = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }

        System.out.println(contron.delUser(name));
        System.out.println("执行成功！");
        //////////////
        System.out.println("--数据查询--");
        System.out.println("请输入用户名：");
        try{
            name = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        System.out.println("请输入密码：");
                try{
            password = buff.readLine();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        contron.getResult(name, password);
    }
    public static void main(String[] args)
    {
        Test test = new Test();
        test.run();
    }

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

package twentithWork.DBFactory;
import java.sql.*;

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

package twentithWork.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import twentithWork.DBFactory.DBUtil;

public class DBContron
{
    public String addUser(String name,String password)//添加用户
    {
        String str = "";
        DBUtil db = new DBUtil();
        Connection conn = db.getConn();
        String sql = "insert into userinfo (username,password)values(?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, password);
            int i = ps.executeUpdate();
            if(i>0)
            {
                str = "添加成功!";
            }else{
                str = "添加失败!";
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            db.close();
        }
        return str;
    }

    public String EditPassword(String name,String password)
    {
        String str = "";
        DBUtil db = new DBUtil();
        Connection conn = db.getConn();
        String sql = "update userinfo set password=? where username=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, password);
            ps.setString(2, name);
            int i = ps.executeUpdate();
            if(i>0)
            {
                str = "修改成功!";
            }else{
                str = "修改失败!";
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            db.close();
        }
        return str;
    }

    public String delUser(String name)
    {
        String str = "";
        DBUtil db = new DBUtil();
        Connection conn = db.getConn();
        String sql1 = "select * from userinfo where username =?";
        try {
            PreparedStatement ps1 = conn.prepareStatement(sql1);
            ps1.setString(1, name);
            ResultSet rs = ps1.executeQuery();
            if(!rs.next())
            {
                str = "无此用户!";
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        //当存在此用户时
        String sql = "delete from userinfo where username=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            int i = ps.executeUpdate();
            if(i>0)
            {
                str = "删除成功!";
            }else{
                str = "删除失败!";
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            db.close();
        }
        return str;
    }

    public void getResult(String username, String password)
    {
        String str = "";
        DBUtil db = new DBUtil();
        Connection conn = db.getConn();
        String sql = "select * from userinfo where username=? and password=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                System.out.println(rs.getString("username")+"\t"+rs.getString("password"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            db.close();
        }
    }

}