博客概要
复习归纳总结之 JDBC 简单增删查改~
JDBC-连接数据库
具体代码:
//连接数据库
public static Connection getConn() {
Connection conn = null;
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
System.out.println("获取驱动成功");
//获取数据库连接
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db_java1ssm?useSSL=true&characterEncoding=utf-8", "root", "123456");
System.out.println("连接数据库成功");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
运行结果:
JDBC-打印全部
具体代码:
/**
* 打印全部
*/
@Override
public void printAllU() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
ResultSet rs = null;
//键盘提示
System.out.println("全体信息:");
//准备sql语句
String sql = "select * from tb_user";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
//产生结果
rs = pstm.executeQuery();
//处理结果
while (rs.next()) {
System.out.println("*用户编号:" + rs.getInt(1) + ",\t*用户名称:" + rs.getString(2) + ",\t*用户密码:" + rs.getString(3) + ",\t*用户类型:" + rs.getInt(4));
}
//释放连接
DBUtil.closeConn(rs, pstm, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
运行结果:
JDBC-新增
具体代码:
/**
* 新增
* @return true,表示成功;false,表示失败
*/
@Override
public boolean doAddU() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
//键盘:提示+输入
System.out.print("输入新增姓名:");
String ausername = in.next();
System.out.print("输入新增密码:");
String apassword = in.next();
System.out.print("输入新增类型:");
int atid = in.nextInt();
//准备sql语句
String sql = "insert into tb_user values(null,?,?,?) ";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
pstm.setString(1, ausername);
pstm.setString(2, apassword);
pstm.setInt(3, atid);
//产生结果
pstm.executeUpdate();
//处理结果
// System.out.println("新增成功");
//释放连接
pstm.close();
conn.close();
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
运行结果:
JDBC-删除
具体代码:
/**
* 删除
* @return true,表示成功;false,表示失败
*/
@Override
public boolean doDelU() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
//键盘:提示+输入
System.out.print("输入删除id:");
int did = in.nextInt();
//准备sql语句
String sql = "delete from tb_user where uid=?";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
pstm.setInt(1, did);
//产生结果
pstm.executeUpdate();
//处理结果
// System.out.println("删除成功");
//释放连接
pstm.close();
conn.close();
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
运行结果:
JDBC-id查
具体代码:
/**
* id查
*/
@Override
public void doSelById() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
ResultSet rs = null;
//键盘:提示+输入
System.out.print("输入查询id:");
int sid = in.nextInt();
//准备sql语句
String sql = "select * from tb_user where uid=?";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
pstm.setInt(1, sid);
//产生结果
rs = pstm.executeQuery();
//处理结果
System.out.println("查询结果:");
while (rs.next()) {
System.out.println("*用户编号:" + rs.getInt(1) + ",\t*用户名称:" + rs.getString(2) + ",\t*用户密码:" + rs.getString(3) + ",\t*用户类型:" + rs.getInt(4));
}
//释放连接
DBUtil.closeConn(rs, pstm, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
运行结果:
JDBC-名字模糊查
具体代码:
/**
* 用户名模糊查询
*/
@Override
public void doSelLikeByName() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
ResultSet rs = null;
//键盘:提示+输入
System.out.print("输入查询用户名:");
String sname = in.next();
//准备sql语句
String sql="select * from tb_user where username like '%"+sname+"%'";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
//产生结果
rs = pstm.executeQuery();
//处理结果
System.out.println("查询结果:");
while (rs.next()) {
System.out.println("*用户编号:" + rs.getInt(1) + ",\t*用户名称:" + rs.getString(2) + ",\t*用户密码:" + rs.getString(3) + ",\t*用户类型:" + rs.getInt(4));
}
//释放连接
DBUtil.closeConn(rs, pstm, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
运行结果:
JDBC-修改
具体代码:
/**
* 修改
* @return true,表示成功;false,表示失败
*/
@Override
public boolean doUdpU() {
//连接数据库
Connection conn = DBUtil.getConn();
PreparedStatement pstm = null;
ResultSet rs = null;
//键盘:提示+输入
System.out.print("输入修改id:");
int uid = in.nextInt();
System.out.print("输入修改用户名:");
String uusername = in.next();
System.out.print("输入修改密码:");
String upassword = in.next();
System.out.print("输入修改类型:");
int utid = in.nextInt();
//准备sql语句
String sql = "update tb_user set username=?,password=?,tid=? where uid=?";
try {
//选中语句并执行——预编译处理
pstm = conn.prepareStatement(sql);
pstm.setString(1, uusername);
pstm.setString(2, upassword);
pstm.setInt(3, utid);
pstm.setInt(4, uid);
//产生结果
pstm.executeUpdate();
//处理结果
// System.out.println("修改成功");
System.out.println("修改结果:");
System.out.println("*用户编号:" + uid + ",\t*用户名称:" + uusername + ",\t*用户密码:" + upassword + ",\t*用户类型:" + utid);
//释放连接
pstm.close();
conn.close();
return true;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
运行结果:
JDBC-释放资源
具体代码:
//释放资源
public static void closeConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws Exception {
if (rs != null) {
rs.close();
}
if (pstm != null) {
pstm.close();
}
if (conn != null) {
conn.close();
}
}
JDBC-集成
具体代码:
public void getAdmin() {
boolean flag = true;
while (flag) {
System.out.println("------------------------欢迎使用系统~------------------------");
System.out.println("-------1.新增\t2.删除\t3.修改\t4.查id\t5.全查\t6.名字模糊查询\t7.返回\t8.退出系统-------");
System.out.print("-----------------请选择菜单(1-7):");
int s = in.nextInt();
switch (s) {
case 1:
usi.doAddU();
break;
case 2:
usi.doDelU();
break;
case 3:
usi.doUdpU();
break;
case 4:
usi.doSelById();
break;
case 5:
usi.printAllU();
break;
case 6:
usi.doSelLikeByName();
break;
case 7:
System.out.println();
getAll();
break;
case 8:
System.exit(0);
break;
default:
break;
}
System.out.print("是否继续(Y/N):");
String str = in.next();
if (str.equals("Y") || str.equals("y")) {
flag = true;
System.out.println();
} else if (str.equals("N") || str.equals("n")) {
System.exit(0);
}else {
System.out.println("输入有误");
System.exit(0);
}
}
}
运行结果:
总结
JDBC最最基础的增删查改知识了吧这是 = =
补充
JDBC创建七步曲~
1.加载数据库驱动
Class.forName(“com.mysql.jdbc.Driver”);
System.out.println(“获取驱动成功”);
2.获取数据库连接
conn = DriverManager.getConnection
(“jdbc:mysql://127.0.0.1:3306/(数据库名)1ssm?>useSSL=true&characterEncoding=utf-8”, “(数据库账户名)”, “(数据库密码)”);
System.out.println(“连接数据库成功”);
3.准备sql语句
String sql = " ";
4.选中语句并执行——预编译处理
PreparedStatement pstm = conn.prepareStatement(sql);
5.产生结果
ResultSet rs = pstm.executeQuery();
6.处理结果
//条件判断
while( ){ }
if( ){ }
//输出
System.out.println()
7.释放连接
rs.close();
pstm.close();
conn.close();