JDBC基础学习之增删改查(三)
程序员文章站
2022-07-12 16:10:11
...
一、准备工作，创建表人员信息表(t_person)、用户表(user)、机构信息表(t_org)
sql脚本：
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50162
Source Host : localhost:3306
Source Database : demo
Target Server Type : MYSQL
Target Server Version : 50162
File Encoding : 65001
Date: 2017-12-02 10:07:32
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_org
-- ----------------------------
DROP TABLE IF EXISTS `t_org`;
CREATE TABLE `t_org` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`pid` int(4) DEFAULT NULL,
`name` varchar(100) NOT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_org
-- ----------------------------
INSERT INTO `t_org` VALUES ('1', '0', '实施组', '调研、实施');
INSERT INTO `t_org` VALUES ('2', '0', '开发组', '开发');
INSERT INTO `t_org` VALUES ('3', '0', '财务部', '做账、');
INSERT INTO `t_org` VALUES ('4', '0', '人力资源部', '人力资源');
-- ----------------------------
-- Table structure for t_person
-- ----------------------------
DROP TABLE IF EXISTS `t_person`;
CREATE TABLE `t_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`oid` int(4) DEFAULT NULL,
`name` varchar(100) NOT NULL,
`sex` char(1) NOT NULL,
`address` varchar(200) DEFAULT NULL,
`age` int(4) DEFAULT NULL,
`phone` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_person
-- ----------------------------
INSERT INTO `t_person` VALUES ('1', '1', '张三', '1', 'xxxxx', '20', '13984848326');
INSERT INTO `t_person` VALUES ('2', '1', '张飞', '1', 'xxxxx', '22', '10086');
INSERT INTO `t_person` VALUES ('3', '2', '杨过', '1', 'xxxxx', '25', '10000');
INSERT INTO `t_person` VALUES ('4', '3', '小龙女', '0', 'xxxxx', '25', '10086');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(4) NOT NULL,
`pid` int(11) DEFAULT NULL,
`user_name` varchar(50) NOT NULL,
`password` varchar(50) DEFAULT NULL,
`create_time` bigint(20) DEFAULT NULL,
`expired_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '1', 'zhangsan', '123456', null, null);
INSERT INTO `user` VALUES ('2', '2', 'zhangfei', '123456', null, null);
INSERT INTO `user` VALUES ('3', '3', 'yangguo', '123456', null, null);
INSERT INTO `user` VALUES ('4', '4', 'xiaolongnv', '123456', null, null);
二、查询出人员表中的所有信息
public class Test {
public static void main(String[] args) {
Connection conn = null;
String url = "jdbc:mysql://127.0.0.1:3306/demo";
String user = "root";
String password = "root_123";
ResultSet rs = null;
Statement stmt = null;
try {
// 第一步：加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 第二步：得到链接
conn = DriverManager.getConnection(url, user, password);
//第三步：创建执行SQL语句的statement
stmt = conn.createStatement();
String sql = "select * from t_person";
//第四步：处理执行的结果
rs = stmt.executeQuery(sql);
while (rs.next()) {
StringBuffer sb = new StringBuffer();
sb.append("id:" + rs.getInt("id") + "\t")
.append("name:" + rs.getString("name") + "\t")
.append("address:" + rs.getString("address") + "\t")
.append("sex:" + rs.getString("sex") + "\t")
.append("age:" + rs.getInt("age") + "\t")
.append("phone:" + rs.getString("phone") + "\t");
System.out.println(sb.toString());
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//第五步：释放资源
if(null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
三、创建表t_test
public class Test2 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
String url = "jdbc:mysql://127.0.0.1:3306/demo";
String user = "root";
String password = "root_123";
try {
//第一步：加载驱动
Class.forName("com.mysql.jdbc.Driver");
//第二步：得到链接
conn = DriverManager.getConnection(url, user, password);
//第三步：创建执行SQL的statement语句
stmt = conn.createStatement();
StringBuffer sbSql = new StringBuffer();
sbSql.append("create table t_test( ")
.append("id int(4) primary key not null, ")
.append("user_name varchar (20) not null ")
.append(");");
stmt.executeUpdate(sbSql.toString());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//第四步：释放资源
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
思考：举一反三，大家可以尝试下删除、修改之类的操作。
四、连接数据库的工具类DBUtil
由于对数据库常见的操作是CRUD，并且进行操作时基本的步骤都相似，因此我们把数据库的连接和资源的关闭进行封装.
package com.zlt.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 将数据库的连接以及资源的释放封装成一个通用的工具类
*
* @author 涛声一笑 289836653
*
*/
public class DBUtil {
/**
* 得到数据库连接
* @return
*/
public static Connection getConnection() {
Connection conn = null;
String url = "jdbc:mysql://127.0.0.1:3306/demo";
String user = "root";
String password = "root_123";
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放结果集资源
* @param rs
*/
public static void close(ResultSet rs){
if(null != rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放执行SQL的statement资源
* @param stmt
*/
public static void close(Statement stmt){
if(null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放数据库连接Connection
* @param conn
*/
public static void close(Connection conn){
if(null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
单元测试
package com.zlt.jdbc.util;
import junit.framework.TestCase;
public class DBUtilTest extends TestCase {
protected void setUp() throws Exception {
super.setUp();
}
public void testGetConnection() {
System.out.println(DBUtil.getConnection());
}
}
五、将查询的结果与JAVA对象模型联系起来
5.1建立对象模型(JavaBean)
package com.zlt.jdbc;
/**
* 建立关系模型与对象模型之间的映射
* 对象模型实体Person与关系模型t_person（暂不考虑关系与关系之间的关联）
* @author 涛声一笑 289836653
*
*/
public class Person {
private int id;
private int oid;
private String name;
private String sex;
private String address;
private int age;
private String phone;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getOid() {
return oid;
}
public void setOid(int oid) {
this.oid = oid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
5.2根据人员ID查询出人员信息
package com.zlt.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.zlt.jdbc.util.DBUtil;
public class Test3 {
public static void main(String[] args) {
Person p = findPersonById(1);
System.out.println("id:" + p.getId() +"name:" +p.getName() + "age:" + p.getAge() + "sex:" + p.getSex() + "oid:" + p.getOid() + "address:" + p.getAddress() +"phone:" + p.getPhone());
}
/**
* 根据人员ID查询出人员的信息
* （建议平时写代码时先把SQL语句调通）
* select id,oid,name,sex,age,address,phone from t_person
* @param id 人员ID
* @return p,p为null时表示没查询到
*/
public static Person findPersonById(int id){
Person p = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
stmt = conn.createStatement();
String sql = "select id,oid,name,sex,age,address,phone from t_person";
rs = stmt.executeQuery(sql);
if(rs.next()) {
p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setOid(rs.getInt("oid"));
p.setSex(rs.getString("sex"));
p.setAge(rs.getInt("age"));
p.setAddress(rs.getString("address"));
p.setPhone(rs.getString("phone"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(stmt);
DBUtil.close(rs);
DBUtil.close(conn);
}
return p;
}
}
5.3查询出姓名为张姓的人员信息
public static List<Person> findPersonByName(String name){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<Person> persons = new ArrayList<Person>();
try {
conn = DBUtil.getConnection();
stmt = conn.createStatement();
String sql = "select id,oid,name,sex,age,address,phone from t_person where name like '%"+name+"%'";
rs = stmt.executeQuery(sql);
while(rs.next()) {
Person p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setOid(rs.getInt("oid"));
p.setSex(rs.getString("sex"));
p.setAge(rs.getInt("age"));
p.setAddress(rs.getString("address"));
p.setPhone(rs.getString("phone"));
persons.add(p);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(stmt);
DBUtil.close(rs);
DBUtil.close(conn);
}
return persons;
}
5.4遗留问题:此时使用Statement执行SQL语句时进行拼串，使用该种语句有两个缺点1）有SQL注入问题 2）当控制条件越多，拼串难度越繁琐
写道
推荐使用PreparedStatement语句，其有效的防止sql注入(SQL语句在程序运行前已经进行了预编译,当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1'也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令
5.5使用PrepareStatement语句重写b示例,但是在重写c示例时，发现模糊查询查不出结果但程序没错，因此一般情况下推荐使用P repareStatement，但有时必须使用Statement进行拼串
public static Person findPersonById(int id){
Person p = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select id,oid,name,sex,age,address,phone from t_person where id = ?" ;
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setOid(rs.getInt("oid"));
p.setSex(rs.getString("sex"));
p.setAge(rs.getInt("age"));
p.setAddress(rs.getString("address"));
p.setPhone(rs.getString("phone"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(pstmt);
DBUtil.close(rs);
DBUtil.close(conn);
}
return p;
}
举一反三：
写道
1）查询人员编号为1,3,5的人员信息。
2）查询年龄在20-25之间的人员信息
3）将姓名为小龙女的人的住址、电话分别更新为绝情谷底，10008611
4）往人员信息表中插入一位新人员（5,3,周伯通,1,30,四海为家,10086）
5）将张三和张飞两个人从表中删除
6）查询姓名为杨过的用户信息以及人员信息
2）查询年龄在20-25之间的人员信息
3）将姓名为小龙女的人的住址、电话分别更新为绝情谷底，10008611
4）往人员信息表中插入一位新人员（5,3,周伯通,1,30,四海为家,10086）
5）将张三和张飞两个人从表中删除
6）查询姓名为杨过的用户信息以及人员信息
上一篇： JDBC基础学习之事务控制(四)
下一篇： 勇于放弃 才好前行 交通生活工作
推荐阅读