通讯录2.0 控制台 SQL2005

连接数据库类

package com.hyjy.conn;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class Conn {


private static Connection conn = null;
private static Statement stmt = null;
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(
"jdbc:sqlserver://localhost:1433;DatabaseName=CONTACT",
"sa", "sa");
} catch (Exception e) {
e.printStackTrace();
}
}


public static Connection getConn() {
return conn;
}
}

账户操作和起始类

package com.hyjy.dao;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;


import com.hyjy.conn.Conn;


public class AccountDAO {
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;


public String idx; // 全局变量idx 用来存储登陆的账号ID 在普通用户的增删改查中用来判断该联系人是否是自己添加
Scanner in = new Scanner(System.in);


public void close() { // 关闭类
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}


public void welcom() throws Exception { // 起始类
System.out.println();
System.out.println();
System.out.println();
System.out.println("************通讯录**************");
System.out.println("请选择操作：【1】登陆                 【2】退出           ");
int num = in.nextInt();
if (num == 1) {
login();
} else if (num == 2) {
System.out.println("谢谢使用！");
} else {
System.out.println("输入有误！");
welcom();
}
}


public void login() throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("请输入账号：");
String id = in.next();
idx = id;
System.out.println("请输入密码：");
String password = in.next();
String[] str = loginCheak(id, password); // 调用loginCheak方法
// 将该方法的返回值存贮在一个数组中
if (str[0].equals("1")) { // 如果数组的第一个数字是1， 那么登陆成功
if (str[1].equals("1")) { // 如果数组的第二个数字是1，那么是管理员登陆
System.out.println("管理员账户登陆成功！");
menu(1, idx);
} else {
System.out.println("普通用户登陆成功！");
menu(0, idx);
}
} else {
System.out.println("登陆失败，账号或密码输入错误！");
welcom();
}


}


/**
* @param id
* @param password
*            判断账号密码是否正确，定义的返回值类型是数组，所以最后返回一个数组
* @throws Exception
*/
public String[] loginCheak(String id, String password) throws Exception {
String[] str = new String[] { "0", "" };
conn = Conn.getConn();
stmt = conn.createStatement();
String sele = "select * from TBL_ACCOUNT where ID = '" + id
+ "' and PASSWORD = '" + password + "'";
rs = stmt.executeQuery(sele);
if (rs.next()) {
str[0] = "1";
str[1] = rs.getString("MORU");
}
// close();
return str;
}


/**
* @param a
* @throws Exception
*             登陆后的菜单界面
*/
public void menu(int a, String idx) throws Exception {
if (a == 1) { // 判断是管理员登陆还是普通用户登陆
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户");
System.out.println("请选择操作：【1】账户管理  【2】通讯录管理");
int num = in.nextInt();
if (num == 1) {


accountManage();


} else if (num == 2) {
managerContact();
} else {
System.out.println("输入错误！");
menu(a, idx);
}
} else {
userContact(idx);
}
}


public void accountManage() throws Exception { // 管理员账户管理
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 账户管理");
System.out.println("请选择操作：【1】添加账户  【2】修改账户");
System.out.println("                        【3】删除账户  【4】查看账户");
System.out.println("                        【5】返回上一级");
int num = in.nextInt();
if (num == 1) {
addAccount();
} else if (num == 2) {
updateAccount();
} else if (num == 3) {
deleteAccount();
} else if (num == 4) {
selectAccount();
} else if (num == 5) {
menu(1, idx);
} else {
System.out.println("输入有误！");
accountManage();
}


}


public void managerContact() throws Exception { // 管理员通讯录管理
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 通讯录管理");
System.out.println("请选择操作：【1】添加联系人  【2】修改联系人");
System.out.println("                        【3】删除联系人  【4】查看联系人");
System.out
.println("                        【5】退出              【6】切换用户");
int num = in.nextInt();
if (num == 1) {
new ContactDAO().addContact(1, idx);
} else if (num == 2) {
new ContactDAO().update(idx, 1);
} else if (num == 3) {
new ContactDAO().delete(idx, 1);
} else if (num == 4) {
new ContactDAO().select(idx, 1);
} else if (num == 5) {
System.out.println("谢谢使用！");
} else if (num == 6) {
welcom();
} else {
System.out.println("输入有误！");
managerContact();
}
}


public void userContact(String idx) throws Exception { // 普通用户通讯录管理
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：普通账户");
System.out.println("请选择操作：【1】添加联系人  【2】修改联系人");
System.out.println("                        【3】删除联系人  【4】查看联系人");
System.out
.println("                        【5】退出              【6】切换用户");
int num = in.nextInt();
if (num == 1) {
new ContactDAO().addContact(0, idx);
} else if (num == 2) {
new ContactDAO().update(idx, 0);
} else if (num == 3) {
new ContactDAO().delete(idx, 0);
} else if (num == 4) {
new ContactDAO().select(idx, 0);
} else if (num == 5) {
System.out.println("谢谢使用！");
} else if (num == 6) {
welcom();
} else {
System.out.println("输入有误！");
userContact(idx);
}
}


/**
* @throws Exception
* 
*             管理员账户管理之添加账户
*/
public void addAccount() throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 账户管理==> 添加账户");
System.out.println("请输入新的账户ID：");
String id = in.next();
conn = Conn.getConn(); // 获取加载和连接
stmt = conn.createStatement();
rs = stmt.executeQuery("select ID from TBL_ACCOUNT where ID = '" + id
+ "'");
if (rs.next()) { // 判断账号是否已经存在
System.out.println("该账号已经存在！");
addAccount();
} else {
System.out.println("请输入密码：");
String password = in.next();
stmt.executeUpdate("insert into TBL_ACCOUNT (ID, PASSWORD, MORU)values ('"
+ id + "', '" + password + "', '0')");
System.out.println("账号添加成功！");
accountManage();
}
}


/**
* @throws Exception
*             管理员账户管理之修改账户
*/
public void updateAccount() throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 账户管理==> 修改账户");
conn = Conn.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select ID, ANO from TBL_ACCOUNT ");
if (rs != null) {
while (rs.next()) {
System.out.println(rs.getString("ANO") + "   "
+ rs.getString("ID"));
}
System.out.println("请输入要修改的账号编号：");
int num = in.nextInt();
System.out.println("请输入新的密码：");
String newPassword = in.next();
int s = stmt.executeUpdate("update TBL_ACCOUNT set PASSWORD = '"
+ newPassword + "' where ANO = '" + num + "'");


if (s > 0) {
System.out.println("修改成功！");
} else {
System.out.println("修改失败，该账号不存在！");
}
}


accountManage();
}


/**
* @throws Exception
*             管理员账户管理之删除账户
*/
public void deleteAccount() throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 账户管理==> 删除账户");
conn = Conn.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_ACCOUNT where ANO > 1");
if (rs != null) {
while (rs.next()) {
System.out.println(rs.getString("ANO") + "   "
+ rs.getString("ID"));
}
}
System.out.println("请输入要删除的账号的编号：");
int num = in.nextInt();
int s = stmt.executeUpdate("delete from TBL_ACCOUNT where ANO = '"
+ num + "'");
if (s > 0) { // 判断账户是否删除成功
System.out.println("删除成功！");
} else {
System.out.println("删除失败，该账号不存在！");
}
accountManage();
}


/**
* @throws Exception
* 
*             列出全部联系人信息
*/
public void selectAccount() throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
System.out.println("当前登陆：管理员账户  ==> 账户管理==> 查看账户");
conn = Conn.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_ACCOUNT ");
if (rs != null) {
System.out.println("\t" + "\t" + "\t" + "账号" + "\t" + "密码");
while (rs.next()) {
System.out.println(rs.getString("ID") + "   "
+ rs.getString("PASSWORD"));
}
}
accountManage();
}
}

通讯录数据库操作类

package com.hyjy.dao;


import java.sql.*;
import java.util.Scanner;


import com.hyjy.conn.Conn;
import com.hyjy.pojo.Contact;


public class ContactDAO {


Scanner in = new Scanner(System.in); // 输入语句
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;


// //定义数据库加载连接对象
// public void close() {
// try {
// if (rs != null) {
// rs.close();
// }
// if (stmt != null) {
// stmt.close();
// }
// if (conn != null) {
// conn.close();
// }
// } catch (Exception e) {
// e.printStackTrace();
// }
// } //关闭连接


public void addContact(int a, String idx) throws Exception {


System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
if (a == 1) { // 判断是管理员登陆还是普通用户登陆
System.out.println("当前登陆：管理员账户  ==> 通讯录管理==>添加联系人");
} else {
System.out.println("当前登陆：普通账户  ==> 通讯录管理==>添加联系人");
}
System.out.println("请输入联系人姓名："); // 输入相应的联系人信息
String name = in.next();
System.out.println("请输入联系人性别：");
String sex = in.next();
System.out.println("请输入联系人电话：");
String phon = in.next();
System.out.println("请输入联系人地址：");
String adress = in.next();
Contact con = new Contact(name, sex, phon, adress); // 调用Contact的带参数的构造方法
// 对联系人信息变量赋值
addCon(con, a, idx); // 调用添加方法 将Contact的对象作为参数
}


public void addCon(Contact con, int a, String idx) throws Exception { // 添加联系人
conn = Conn.getConn(); // 获取加载连接
stmt = conn.createStatement();
stmt.executeUpdate("insert into TBL_CONTACT (CNAME, SEX, PHON, ADRESS, ID) values ('"
+ con.getName() // 添加联系人SQL语句
+ "','"
+ con.getSex()
+ "','"
+ con.getPhon()
+ "','"
+ con.getAdress() + "','" + idx + "')");
System.out.println("添加成功！");
// close();
if (a == 1) {
new AccountDAO().managerContact(); // 返回上一级
} else {
new AccountDAO().userContact(idx);
}
}


public void update(String idx, int a) throws Exception {


System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
if (a == 1) {
System.out.println("当前登陆：管理员账户  ==> 通讯录管理==>修改联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT ");
} else {
System.out.println("当前登陆：普通账户  ==> 通讯录管理==>修改联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT where ID = '"
+ idx + "'");
}
if (rs != null) {
System.out.println();
System.out.println();
while (rs.next()) { // 循环输出所有联系人信息
System.out.println(rs.getString("CNO") + "  "
+ rs.getString("CNAME") + "   " + rs.getString("SEX")
+ "   " + rs.getString("PHON") + "   "
+ rs.getString("ADRESS"));
}
}
System.out.println("请选择操作：【1】输入要修改的联系人编号  【2】返回上一级");
int nu = in.nextInt();
if (nu == 1) {
System.out.println("请输入要修改的联系人编号：");
String cno = in.next();
System.out.println("请选择要修改的信息：【1】姓名【2】性别【3】电话【4】地址");
int num = in.nextInt();
if (num == 1) {
System.out.println("请输入新的姓名：");
String nam = in.next();
int s = stmt.executeUpdate("update TBL_CONTACT set CNAME = '"
+ nam + "' where CNO = '" + cno + "'");
if (s > 0) { // 判断是否修改成功
System.out.println("修改成功！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("修改失败，该联系人不存在！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}
} else if (num == 2) {
System.out.println("请输入新的性别：");
String se = in.next();
int s = stmt.executeUpdate("update TBL_CONTACT set SEX = '"
+ se + "' where CNO = '" + cno + "'");
if (s > 0) {
System.out.println("修改成功！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("修改失败，该联系人不存在！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}
} else if (num == 3) {
System.out.println("请输入新的电话：");
String pho = in.next();
int s = stmt.executeUpdate("update TBL_CONTACT set PHON = '"
+ pho + "' where CNO = '" + cno + "'");
if (s > 0) {
System.out.println("修改成功！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("修改失败，该联系人不存在！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}
} else if (num == 4) {
System.out.println("请输入新的地址：");
String ads = in.next();
int s = stmt.executeUpdate("update TBL_CONTACT set ADRESS = '"
+ ads + "' where CNO = '" + cno + "'");
if (s > 0) {
System.out.println("修改成功！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("修改失败，该联系人不存在！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}
} else {
System.out.println("输入错误！");


}
} else if (nu == 2) {
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("输入错误！");
update(idx, a);
}
}


public void delete(String idx, int a) throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
if (a == 1) {
System.out.println("当前登陆：管理员账户  ==> 通讯录管理==>删除联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT ");
} else {
System.out.println("当前登陆：普通账户  ==> 通讯录管理==>删除联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT where ID = '"
+ idx + "'");
}
if (rs != null) {
while (rs.next()) { // 列举所有联系人信息
System.out.println();
System.out.println();
System.out.println(rs.getString("CNO") + "  "
+ rs.getString("CNAME") + "   " + rs.getString("SEX")
+ "   " + rs.getString("PHON") + "   "
+ rs.getString("ADRESS"));
}
}
System.out.println("请选择操作：【1】输入要删除的联系人编号  【2】返回上一级");
int num = in.nextInt();
if (num == 1) {
System.out.println("请输入要删除的联系人编号：");
String cno = in.next();
int s = stmt.executeUpdate("delete from TBL_CONTACT where CNO = '"
+ cno + "'");
if (s > 0) { // 判断是否删除成功
System.out.println("删除成功！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("删除失败，该联系人不存在！");
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}
} else if (num == 2) {


if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
} else {
System.out.println("输入错误！");
delete(idx, a);
}


}


public void select(String idx, int a) throws Exception {
System.out.println();
System.out.println();
System.out.println();
System.out.println("***********通讯录登陆**********");
if (a == 1) {
System.out.println("当前登陆：管理员账户  ==> 通讯录管理==>查看联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT ");
} else {
System.out.println("当前登陆：普通账户  ==> 通讯录管理==>查看联系人");
conn = new Conn().getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from TBL_CONTACT where ID = '"
+ idx + "'");
}
System.out.println("联系人信息如下：");
System.out.println();
System.out.println();
if (rs != null) {
while (rs.next()) {
System.out.println(rs.getString("CNAME") + "   "
+ rs.getString("SEX") + "   " + rs.getString("PHON")
+ "   " + rs.getString("ADRESS"));
}
}
if (a == 1) {
new AccountDAO().managerContact();
} else {
new AccountDAO().userContact(idx);
}
}


}

通讯录表字段赋值类

package com.hyjy.pojo;


public class Contact {


private String name;
private String sex;
private String phon;
private String adress;


public Contact() {
super();
}


public Contact(String name, String sex, String phon, String adress) {
super();
this.name = name;
this.sex = sex;
this.phon = phon;
this.adress = adress;
}


public String getName() {
return name;
}


public void setName(String name) {
this.name = name;
}


public String getSex() {
return sex;
}


public void setSex(String sex) {
this.sex = sex;
}


public String getPhon() {
return phon;
}


public void setPhon(String phon) {
this.phon = phon;
}


public String getAdress() {
return adress;
}


public void setAdress(String adress) {
this.adress = adress;
}
}

测试类

package com.hyjy.test;


import com.hyjy.dao.AccountDAO;


public class Test {
public static void main(String[] args) throws Exception {


new AccountDAO().welcom();
}
}