目录
一、总结JDBC所用到的类或接口
类或接口
|
描述
|
DriverManager
类
|
驱动管理
|
Connection
接口
|
数据库连接对象
|
Statement
接口
|
语句对象
|
PreparedStatement
接口
|
语句对象
|
ResultSet
接口
|
结果集对象
|
方法
|
所属类或接口
|
描述
|
getConnection
(
url
,
user
,
password
)
|
DriverManager
类
|
获取数据库连接对象
|
createStatement
()
|
Connection
接口
|
获取
statement
对象
|
prepareStatement
(
sql
)
|
Connection
接口
|
获取preparedStatement对象
|
set
数据类型
(
第几个问号,数据值)
|
PreparedStatement
接口
|
给问号赋值
|
excute
()
|
Statement
接口
&PreparedStatement
接口
|
执行
sql
,返回boolean
|
excuteUpdate()
|
Statement
接口
&PreparedStatement
接口
|
执行
sql
,返回
int
|
excureQuery()
|
Statement
接口
&PreparedStatement
接口
|
执行
sql
,返回结果
集对象
|
next()
|
ResultSet
接口
|
判断当前行有没有数
据
|
get
数据类型
(
数据库的字段名)
|
ResultSet
接口
|
获取指定数据类型的数据
|
close()
|
Statement
接口
&PreparedStatement
接口
&ResultSet
接口
|
关闭连接
|
基于
preparedStatement,实现用户登录,验证
sql
注入攻击
public class Demo01 {
public static int updateUser(int id,String columnName,String columnValue) throws SQLException {
//获取数据库连接
Connection connection = JdbcUtil.getCon();
//获取语句对象
PreparedStatement preparedStatement = connection.prepareStatement("update user set "+columnName+" = ? where id = ?");
//给问好赋值
preparedStatement.setString(1,columnValue);
preparedStatement.setInt(2,id);
//执行sql
int result = preparedStatement.executeUpdate();
//关闭资源
JdbcUtil.closeAll(null,preparedStatement,connection);
return result;
}
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入id:");
int id = sc.nextInt();
System.out.println("请输入字段名:");
String columnName = sc.next();
System.out.println("请输入字段值:");
String columnValue = sc.next();
try {
int result = updateUser(id,columnName,columnValue);
if (result > 0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、PreparedStatement&Statement
1.preparedStatement是
statement
的子接口
2.preparedStatement可以防止
sql
注入攻击,是因为有预编译功能,每次去执行的时候,只改变传参
3.preparedStatement提高查询效率
三、分包思想
封装JdbcUtil
封装javabean
多表联查
public class Demo02 {
public static List<Emp> getList() throws SQLException {
List<Emp> list = new ArrayList<>();
//获取数据库连接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = connection.prepareStatement("select ename,job,mgr,hiredate,dname from emp e inner join dept d on e.deptno = d.deptno");
//执行sql
ResultSet resultSet = preparedStatement.executeQuery();
//遍历
while (resultSet.next()){
Emp emp = new Emp(resultSet.getString("ename"),resultSet.getString("job")
,resultSet.getInt("mgr"),resultSet.getString("hiredate"),resultSet.getString("dname"));
list.add(emp);
}
//关闭资源
JdbcUtil.closeAll(resultSet,preparedStatement,connection);
return list;
}
public static void main(String[] args) {
try {
List<Emp> list = getList();
for (Emp emp :list) {
System.out.println(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
案例:基于JDBC编程,实现员工管理系统
1.登录,登陆成功则进入管理页面,登陆失败则重新登录 2进入管理页面后,可选新增员工、修改员工、根据员工姓名模糊查询员工、删除员工、查询所有员工、退出登录 3.新增员工:键盘输入新增员工信息,成功后返回员工列表及选择页面 4.修改员工:键盘输入要修改的员工编号后,进行修改,成功后返回员工列表及选择页面 5.根据员工姓名模糊查询员工:键盘输入员工姓名,进行模糊查询,返回所有结果及选择页面 6.删除员工:键盘输入要删除的员工编号后,成功后返回员工列表及选择页面 7.查询所有员工:进行结果的展示及选择页面 8.退出登录:返回登录页面
public class Lian {
static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
//登录
login();
}
//登录
public static void login() {
System.out.print("请输入用户名:");
String username = sc.next();
System.out.print("请输入密码:");
String password = sc.next();
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement("select * from user where user_name = ? and password = ?");
//给问号赋值
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
//执行sql
resultSet = preparedStatement.executeQuery();
//处理结果
if (resultSet.next()) {
System.out.println("登陆成功");
choose();
}
else {
System.out.println("用户名或密码输入错误,请重新输入");
login();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(resultSet,preparedStatement,connection);
}
}
//功能选项
public static void choose() {
System.out.println("------------------欢迎进入员工管理系统------------------");
System.out.println("1.新增员工");
System.out.println("2.修改员工");
System.out.println("3.根据员工姓名模糊查询员工");
System.out.println("4.删除员工");
System.out.println("5.查询所有员工");
System.out.println("6.退出登录");
System.out.print("请输入你的选择:");
int num = sc.nextInt();
switch (num) {
case 1:
addEmp();
choose();
break;
case 2:
updateEmp();
choose();
break;
case 3:
unClearShowEmp();
choose();
break;
case 4:
deleteEmp();
choose();
break;
case 5:
showEmp();
choose();
break;
case 6:
System.out.println("感谢使用,欢迎下次光临");
return;
default:
System.out.println("输入有误,请重新输入!!!");
choose();
break;
}
}
//1.新增员工
public static void addEmp(){
System.out.println("-------------新增员工-------------");
System.out.print("请输入员工编号:");
int emp_no = sc.nextInt();
System.out.print("请输入员工姓名:");
String emp_name = sc.next();
System.out.print("请输入员工部门编号:");
int dept_no = sc.nextInt();
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement("insert into emp(empno,ename,deptno) values(?,?,?)");
//给问号赋值
preparedStatement.setInt(1, emp_no);
preparedStatement.setString(2, emp_name);
preparedStatement.setInt(3, dept_no);
//执行sql
boolean result = preparedStatement.execute();
//处理结果
if (result) {
System.out.println("新增失败");
addEmp();
}
else {
System.out.println("新增成功");
showEmp();
choose();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(null,preparedStatement,connection);
}
}
//2.修改员工
public static void updateEmp(){
System.out.println("-------------修改员工-------------");
System.out.print("请输入要修改的员工编号:");
int emp_no = sc.nextInt();
System.out.print("请输入要修改的字段名:");
String columnName = sc.next();
System.out.print("请输入修改后的字段值:");
String columnValue = sc.next();
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement("update emp set "+columnName+" = ? where empno = ?");
//给问号赋值
preparedStatement.setString(1, columnValue);
preparedStatement.setInt(2, emp_no);
//执行sql
int result = preparedStatement.executeUpdate();
//处理结果
if (result>0) {
System.out.println("修改成功");
showEmp();
choose();
}
else {
System.out.println("修改失败");
updateEmp();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(null,preparedStatement,connection);
}
}
//3.根据员工姓名模糊查询员工
public static void unClearShowEmp(){
System.out.println("-------------根据员工姓名模糊查询员工-------------");
System.out.print("请输入要查询的员工姓名:");
String name = sc.next();
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement("select empno 员工编号,ename 员工姓名,deptno 部门编号 from emp where ename like '%"+name+"%'");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int emp_no = resultSet.getInt("员工编号");
System.out.print(emp_no+" ");
String emp_name = resultSet.getString("员工姓名");
System.out.print(emp_name+" ");
int dept_no = resultSet.getInt("部门编号");
System.out.println(dept_no);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(resultSet,preparedStatement,connection);
}
}
//4.删除员工
public static void deleteEmp(){
System.out.println("-------------删除员工-------------");
System.out.print("请输入员工编号:");
int emp_no = sc.nextInt();
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement("delete from emp where empno = ?");
//给问号赋值
preparedStatement.setInt(1, emp_no);
//执行sql
int result = preparedStatement.executeUpdate();
//处理结果
if (result>0) {
System.out.println("删除成功");
showEmp();
choose();
}
else {
System.out.println("删除失败");
deleteEmp();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(null,preparedStatement,connection);
}
}
//5.查询所有员工
public static void showEmp(){
//获取链接
Connection connection = JdbcUtil.getCon();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement("select empno 员工编号,ename 员工姓名,deptno 部门编号 from emp");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int emp_no = resultSet.getInt("员工编号");
System.out.print(emp_no+" ");
String emp_name = resultSet.getString("员工姓名");
System.out.print(emp_name+" ");
int dept_no = resultSet.getInt("部门编号");
System.out.println(dept_no);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(resultSet,preparedStatement,connection);
}
}
}