JDBC基础学习之增删改查(三)

2018-01-14 19:08:17来源:作者:人点击

分享

一、准备工作,创建表人员信息表(t_person)、用户表(user)、机构信息表(t_org)

sql脚本:

/*Navicat MySQL Data TransferSource Server : localhostSource Server Version : 50162Source Host: localhost:3306Source Database : demoTarget Server Type : MYSQLTarget Server Version : 50162File Encoding : 65001Date: 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)当控制条件越多,拼串难度越繁琐

写道 CRUDjdbc

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台