Java面向控台的小案例-----通过jdbc操作数据库
1.思路:将JDBC连接Java程序和数据库的一些重复操作。用一个工具类JDBCUITLS实现。
代码如下:
(1)工具类中定义三个属性url\uesr\password并初始化
//定义三个属性
private static String url;
private static String user;
private static String password;
//初始化
static {
InputStream stream = null;
Properties properties = new Properties();
try {
//通过类加载器去获取配置信息,并将配置信息通过Properties对象完成初始化
stream = JDBCUTILS.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(stream);
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
//关闭资源
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
(2)定义一个获取Connection对象的静态方法
public static Connection getConnection() {
Connection connection = null;
try {
//通过DriverManager.getConnection将三个属性url,user,password传进去
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
//返回一个连接对象
return connection;
}
(3)根据需要再去封装其他类似关闭资源的方法,这里就不一一罗列了
2.定义一个实体类去实现简单通过Java控制台去操作数据库的增删改查(针对某一数据库的弄一张表实现)
(1)定义一个简单的操作界面:
public static void getLogin() throws Exception {
Scanner scan = new Scanner(System.in);
System.out.println(“欢迎进入j30201_mysql数据库操作系统”);
s:
while (true) {
System.out.println(“请输入操作选项”);
System.out.println(“1查看数据库内容----2查看emp表信息----”);
System.out.println(“3增加数据--------4修改数据----”);
System.out.println(“5删除数据-------0退出系统”);
int i = scan.nextInt();
switch (i) {
case 1:
//查看数据库内容
getMysql();
break;
case 2:
//查看j230201_mysql中emp表的信息
getTable();
break;
case 3:
//往emp中添加指定字段的数据
setInsert();
break;
case 4:
//指定只能修改emp表中的name属性值,通过id决定
setUpdate();
break;
case 5:
//删除emp表中的数据通过id决定删除哪条
setDelete();
break;
case 0:
break s;
}
}
}
(2)分别去定义相关操作的实现方法:
查看数据库:
public static void getMysql() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
String sql = " ";
try {
//通过工具类JDBCUTILS.getConnection()方法获取Connection对象
connection = JDBCUTILS.getConnection();
//采用PreparedStatement对象防止SQL注入
sql = "show databases";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(connection, statement, resultSet);
}
}
查看j230201_mysql数据库中emp表的信息:
public static void getTable() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
String sql = " ";
try {
connection = JDBCUTILS.getConnection();
sql = "select * from emp ";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
int anInt = resultSet.getInt(1);
String string = resultSet.getString(2);
int anInt1 = resultSet.getInt(3);
double aDouble = resultSet.getDouble(4);
String string1 = resultSet.getString(5);
java.sql.Date date = resultSet.getDate(6);
String string2 = resultSet.getString(7);
System.out.println(anInt + string + anInt1 + aDouble + string1 + date + string2);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(connection, statement, resultSet);
}
}
往emp表中去添加指定字段的数据:
public static void setInsert() {
Scanner scan = new Scanner(System.in);
Connection connection = null;
PreparedStatement statement = null;
String sql = " ";
System.out.println("请输入添加的数据信息:姓名,年龄,工资,入职时间,部门");
try {
connection = JDBCUTILS.getConnection();
sql = "insert into emp(name,age,salary,job,entry_time,dept) values (?,?,?,?,?,?)";
statement = connection.prepareStatement(sql);
statement.setString(1, scan.nextLine());
statement.setInt(2, scan.nextInt());
statement.setDouble(3, scan.nextDouble());
statement.setString(4, scan.nextLine());
//将日期转换成想要的形式
String str = scan.nextLine();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = dateFormat.parse(str);
java.sql.Date date1 = new java.sql.Date(date.getTime());
statement.setDate(5, date1);
statement.setString(6, scan.nextLine());
int i = statement.executeUpdate();
if (i >= 1) {
System.out.println("数据添加成功");
} else {
System.out.println("数据添加失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(connection, statement);
}
}
修改emp表中的name字段属性值,通过id来限制修改的位置:
public static void setUpdate() {
Scanner scan = new Scanner(System.in);
Connection connection = null;
PreparedStatement statement = null;
String sql = " ";
System.out.println("请输入要修改的数据姓名 id");
try {
connection = JDBCUTILS.getConnection();
sql = "update emp set name=? where id=?";
statement = connection.prepareStatement(sql);
statement.setString(1, scan.nextLine());
statement.setInt(2, scan.nextInt());
int i = statement.executeUpdate();
if (i >= 1) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(connection, statement);
}
}
删除emp表中的数据通过id决定删除哪条:
public static void setDelete() {
Scanner scan = new Scanner(System.in);
Connection connection = null;
PreparedStatement statement = null;
String sql = " ";
System.out.println("请输入要修改的数据 id");
try {
connection = JDBCUTILS.getConnection();
sql = "delete from emp where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, scan.nextInt());
int i = statement.executeUpdate();
if (i >= 1) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(connection, statement);
}
}
3.测试
去创建一个测试类,再main方法中去调用控制系统界面的方法。简单测试相关功能,可初步实现。
4.总结:初步完成了一些简单的增删查改的功能,难点在于Date时间的输入和转换上,需要去把输入的时间字符串格式化一下,获得和我们数据库中Date的格式一样,这样就能存储进去。