java 控制台项目 对数据库进行增删改查

[java]代码库

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestConnection {

    /**
     * 定义链接需要的字符串
     */
    private static final String str1 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test3";
    private static final String user = "sa";
    private static final String password = "910627";
    Connection conn;
    PreparedStatement st;
    ResultSet rs;

    /**
     * 加载驱动类
     */
    static {

        try {
            Class.forName(str1);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 建立链接的方法
     * 
     * @return
     */
    private Connection getConnection() {

        try {
            conn = DriverManager.getConnection(url, user, password);

        } catch (Exception e) {
            // TODO: handle exception
        }
        return conn;

    }

    /**
     * 使用prepareStatement来预编译查询语句 然后传参数的值来作为条件查询数据库 返回list
     * 
     * @param id
     * @return
     */
    public List getData(String sql, Object[] array) {
        // SQL语句
        List list = new ArrayList();
        conn = this.getConnection();
        try {
            // 预编译
            st = conn.prepareStatement(sql);
            // 利用方法传入参数
            for (int i = 0; i < array.length; i++) {
                st.setObject(i + 1, array[i]);
            }
            // 执行查询
            rs = st.executeQuery();
            while (rs.next()) {
                Map map = new HashMap();

                ResultSetMetaData rsmd = rs.getMetaData();
                // 以列名为键 存储每一行数据进map
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {

                    map.put(rsmd.getColumnName(i), rs.getObject(i));

                }
                // 将每一个map加入list 这样list的到就是每一行
                list.add(map);

            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 关闭连接
            this.close();
        }
        return list;

    }

    /**
     * 更新数据的方法
     * 
     * @param sql
     * @param array
     * @return
     */
    public int update(String sql, Object array[]) {
        conn = this.getConnection();
        int line = 0;
        try {

            st = conn.prepareStatement(sql);
            // 传参数
            for (int i = 0; i < array.length; i++) {
                st.setObject(i + 1, array[i]);
            }

            line = st.executeUpdate();
            // 判断是否修改成功
            if (line > 0) {
                return line;

            } else {

                System.out.println("更新失败");
            }

        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            // 关闭连接
            this.close();
        }
        return 0;
    }

    /**
     * 关闭连接
     */
    private void close() {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {

                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}
----------------------------------------------------------------------------

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
/**
 * 数据库操作类
 * @author Administrator
 *
 */
public class Handle {
    TestConnection tc = new TestConnection();
    Scanner sc = new Scanner(System.in);
    /**
     * 查询方法
     */
    public void query() {
        System.out.println("1:查询全部");
        System.out.println("2：根据employeeId查询");
        System.out.print("选择你要执行选项");
        // Scanner sc2 = new Scanner(System.in);
        int type2 = sc.nextInt();
        switch (type2) {

        case 1:
            String Sql1 = "select employeeId,employeeName,email,employeeSalary,departmentId from employee where 1=?";
            Object[] array1 = { 1 };
            List list = tc.getData(Sql1, array1);
            /**
             * 取键值 并打印 即为输出的列名 排列 
             */
            Map map2 = (Map) list.get(0);
            // 存键值
            Set set2 = map2.keySet();
            Iterator it2 = set2.iterator();
            while (it2.hasNext()) {
                System.out.print("\\t" + it2.next());
            }

            System.out.println();
            //循环取出 每个行的数据

            for (Object object : list) {
                // list里面是map对象
                Map map = (Map) object;
                // 存键值
                Set set = map.keySet();
                Iterator it = set.iterator();

                while (it.hasNext()) {
                    // 取键值

                    Object key = it.next();

                    // 输出 map里的数据

                    System.out.print("\\t " + map.get(key));

                }
                System.out.println();
            }

            break;
        case 2:
            /**
             * 根据用户输入的员工id进行查询
             */
            System.out.println("输入employeeId：");
            Object object = sc.nextInt();
            Object[] array = { object };
            String Sql2 = "select employeeId,employeeName,email,employeeSalary,departmentId from employee where employeeId =? ";

            List list2 = tc.getData(Sql2, array);
           //输出列名
            Map map3 = (Map) list2.get(0);
            // 存键值
            Set set3 = map3.keySet();
            Iterator it3 = set3.iterator();

            while (it3.hasNext()) {
                System.out.print("\\t" + it3.next());
            }

            System.out.println();
            //循环输出数据
            for (Object object2 : list2) {
                // list里面是map对象
                Map map4 = (Map) object2;
                // 存键值
                Set set4 = map4.keySet();
                Iterator it4 = set4.iterator();

                while (it4.hasNext()) {
                    // 取键值

                    Object key = it4.next();

                    // 输出 map里的数据

                    System.out.print("\\t " + map4.get(key));
                    // System.out.print("\\t"+ map.get(key));

                }
                System.out.println();
            }

            break;
        }
    }

    /**
     * 更新方法
     */
    public void update(){
        System.out.print("请输入employeeId：");
        Object id = sc.next();
        System.out.print("请输入想更新的薪水值：");
        Object salary = sc.next();
        //根据用户输入的员工号来修改薪水值并判断是否执行成功
        String sql = "update employee set employeeSalary = ? where  employeeId = ? " ;
        Object [] array =  { salary, id  };
        //使用TestConnection的update方法
        int line =  tc.update(sql, array);
        if(line>0){
            System.out.println("信息更新成功！");
        }

    }
    /**
     * 插入方法
     */
    public void insert(){
        System.out.print("请输入employeeId：");
        Object id = sc.next();
        System.out.print("请输入employeeName：");
        Object name = sc.next();
        System.out.print("请输入email：");
        Object email = sc.next();
        System.out.print("请输入employeeSalary：");
        Object salary = sc.next();
        System.out.print("请输入dapartmentId：");
        Object dpId = sc.next();
        Object[] array = {id,name,email,salary,dpId};
        //插入用户输入的数据 并判断是否执行成功
        String sql = "insert into employee values(?,?,?,?,?)";
        int line = tc.update(sql, array);
        if(line>0){
            System.out.println("插入成功！");
        }
    }

    /**
     * 删除方法
     */
    public void delete(){
        System.out.print("请输入想删除的员工号：");
        Object id = sc.next();
        Object [] array = {id};
        //删除用户输入 的员工号的数据并判断是否执行成功
        String sql = "delete from employee where employeeId = ? ";
        int line =  tc.update(sql, array);
            if(line>0){
                System.out.println("删除成功！");
            }
    }

}
-----------------------------------------------------------------------------------------

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
/**
 * 测试类
 * @author Administrator
 *
 */
public class Test {
    public static void main(String[] args) {
        //输出选项
      System.out.println("欢迎光临：");
      System.out.println("1：查询");
      System.out.println("2：更新");
      System.out.println("3：插入");
      System.out.println("4：删除");
      System.out.println("5：退出"+"\\n");
      System.out.print("你想干什么？   请选择：");
      //控制台输入
      Scanner sc = new Scanner(System.in);
      //实例化数据操作类Handle
      Handle hd = new Handle();

      int type = sc.nextInt();
      /**
       * 判断用户选择操作的项
       */
      switch(type){
      case 1:
          //调用Handle查询方法
          hd.query();
          break;
      case 2:
          //更新
          hd.update();
          break;
      case 3:
          //插入
          hd.insert();
          break;
      case 4:
          //删除
          hd.delete();
          break;
      case 5:
          //退出
          System.out.println("程序退出！");
          System.exit(0);
          break;
      }

    }
}
//该片段来自于http://yuncode.net