JDBC连接数据库_DBConnTest.java_20130913

package JDBCDemo;
import java.sql.*;
import java.util.*;
public class DBConnTest {
	public static void main(String[] args) throws SQLException{
		while(true){
			System.out.println("欢迎");
			System.out.println("1:添加会员");
			System.out.println("2:查询会员");
			System.out.println("3:删除会员");
			System.out.println("4:更新会员");
			System.out.println("5:退出");
			Scanner sc = new Scanner(System.in);
			int cmd = sc.nextInt();
			switch (cmd) {
			case 1:add();break;
			case 2:select();break;
			case 3:delect();break;
			case 4:update();break;
			case 5:System.out.println("退出成功");System.exit(0);
			break;
			default:System.out.println("输入有误");break;
			}
		}
	}
	//add
	public static void add() throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("姓名:");
		String name = sc.next();
		System.out.println("性别:");
		String gender = sc.next();
		System.out.println("省份:");
		String status = sc.next();
		System.out.println("电话:");
		String phone = sc.next();
		System.out.println("地址:");
		String address = sc.next();
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into t_member values (?,?,?,?,?)";
		try {
			conn = DBConnection0.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, gender);
			pstmt.setString(3, status);
			pstmt.setString(4, phone);
			pstmt.setString(5, address);
			int isOK = pstmt.executeUpdate();
			if (isOK>0) {
				System.out.println("添加成功");				
			} else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			System.out.println("添加失败");
		}finally{
			pstmt.close();
			conn.close();
			
		}
	}
	//select
	public static void select() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM [member].[dbo].[t_member]";
		try {
			conn = DBConnection0.getConn();//数据库连接对象
			//用于执行sql语句的对象
			pstmt = conn.prepareStatement(sql);//
			rs = pstmt.executeQuery();///结果集
			System.out.print("编号"+" ");
			System.out.print("姓名"+" ");
			System.out.print("性别"+" ");
			System.out.print("省份"+" ");
			System.out.print("电话"+" ");
			System.out.println("地址"+" ");
			while(rs.next()){
				System.out.print(rs.getInt(1)+" ");
				System.out.print(rs.getString(2)+" ");
				System.out.print(rs.getString(3)+" ");
				System.out.print(rs.getString(4)+" ");
				System.out.print(rs.getString(5)+" ");
				System.out.println(rs.getString(6)+" ");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			rs.close();
			pstmt.close();
			conn.close();
		}
	}
	//delect
	public static void delect() throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入会员编号");
		int id = sc.nextInt();
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from member.dbo.t_member where memberID = ?";
		try {
			conn = DBConnection0.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);//干吗用?
			int isOK = pstmt.executeUpdate();
			if (isOK>0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("删除失败");
		}finally{
			pstmt.close();
			conn.close();
		}
	}
	//updatei软件妇女i哦二品v迷那搜谱
	public static void update() throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入会员姓名:");
		String name = sc.next();
		System.out.println("请输入会员编号");
		int id = sc.nextInt();
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update member.dbo.t_member set memberName = ? where memberID = ?";
		try {
			conn = DBConnection0.getConn(); 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, id);
			int isOK = pstmt.executeUpdate();
			if (isOK>0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			// TODO: handle exception
			System.out.println("更新失败");
		}finally{
			pstmt.close();
			conn.close();
		}
	}
}