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

}