JDBC连接数据库操作（三）

利用前两篇的知识，写一个综合的小案例，实现对数据库的增删查改。

表的结构

数据库名为day12表名为person    表有两列，列名分别是id和name

代码

import java.sql.ResultSet;import java.util.Scanner;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;public class DatabaseDemo {    public static void main(String[] args) throws Exception {        boolean flag = true;        int choose;        Scanner sc = new Scanner(System.in);        Connection con = JDBCUtils.getCon();        String sql;        String values;        PreparedStatement psmt = null;        int id;        int line;        do {            System.out.println("==================数据库操作==================");            System.out.println(" 1查询    2插入     3更改     4删除      0退出  ");            System.out.println("============================================");            choose = sc.nextInt();            switch (choose) {            case 2:                sql = "insert into person(name) values(?)";                psmt = (PreparedStatement) con.prepareStatement(sql);                System.out.println("输入姓名：");                values = sc.next();                psmt.setString(1, values);                line = psmt.executeUpdate();                System.out.println("新添加的记录："+line);                break;            case 1:                sql = "select * from person";                psmt = (PreparedStatement) con.prepareStatement(sql);                ResultSet rs = psmt.executeQuery();                System.out.println("id\tname");                while (rs.next()) {                    int idc = rs.getInt("id");                    String name = rs.getString("name");                    System.out.println(idc+"\t"+name);                }                rs.close();                break;            case 3:                sql = "update person set name=? where id=?";                psmt = (PreparedStatement) con.prepareStatement(sql);                System.out.println("请输入新的name：");                values = sc.next();                System.out.println("请输入id号：");                id = sc.nextInt();                psmt.setString(1, values);                psmt.setInt(2, id);                line = psmt.executeUpdate();                System.out.println("新添加的记录："+line);                break;            case 4:                sql = "delete from person where id=?";                psmt = (PreparedStatement) con.prepareStatement(sql);                System.out.println("请输入id号：");                id = sc.nextInt();                psmt.setInt(1, id);                line = psmt.executeUpdate();                System.out.println("新添加的记录："+line);                break;            case 0:                flag = false;                sc.close();                psmt.close();                con.close();                break;            default:                System.out.println("输入值无效");                break;            }        } while (flag);    }}

JDBC工具类

import java.io.FileInputStream;import java.io.InputStream;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;import com.mysql.jdbc.Connection;public class JDBCUtils {    private static String DRIVER;    private static String URL;    private static String USER;    private static String PASSWORD;    // 通过配置文件来获取用户名密码等连接数据库的信息，是一种常用的手段    static {        try {            // Properties可保存在流中或从流中加载,属性列表中每个键及其对应值都是一个字符串            Properties prop = new Properties();            // 使用IO流获得配置文件            InputStream in = new FileInputStream("D:\\setfile.txt");            // 将文件加载到Properties            prop.load(in);            // 通过键来取对应的值            DRIVER = prop.getProperty("driver");            URL = prop.getProperty("url");            USER = prop.getProperty("user");            PASSWORD = prop.getProperty("password");            in.close(); //关闭IO流        } catch (Exception e) {            System.out.println("获取配置文件失败");        }    }    public static Connection getCon() {        try {            // 驱动注册            Class.forName(DRIVER);        } catch (ClassNotFoundException e) {            e.printStackTrace();        }        Connection con = null;        try {            con = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);        } catch (SQLException e) {            e.printStackTrace();        }        return con;    }}

配置文件

文件的位置在D盘的根目录下，里面是以键值对的形式存储的，方便通过使用代码来一一取出。等号前面的是键，等号后面的是对应的值