来源:http://www.bjsxt.com/
一、S03E277_01JDBC_ORM原理、使用Object数组存储一条记录
引用表emp
#右击该properties文件--properties--Resource--Text file encoding,选中other,选择其它编码方式。
#如UTF-8或GBK,这样就能在properties里面输入中文,而不会自动转成Unicode了。
#java中的properties文件是一种配置文件,主要用于表达配置信息。
#文件类型为*.properties,格式为文本文件,文件内容是"键=值"的格式。
#在properties文件中,可以用"#"来作注释
#MySQL连接配置
mysqlDriver=com.mysql.jdbc.Driver
mysqlURL=jdbc:mysql://localhost:3306/sorm
mysqlUser=root
mysqlPwd=mysql
#Oracle连接配置
#...
package com.test.orm;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
static Properties pros = null; //可以帮助读取和处理资源文件中的信息
static { //加载JDBCUtil类的时候调用
pros = new Properties();
try {
pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getMysqlConn(){
try {
Class.forName(pros.getProperty("mysqlDriver"));
return DriverManager.getConnection(pros.getProperty("mysqlURL"),
pros.getProperty("mysqlUser"),pros.getProperty("mysqlPwd"));
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
//可以重载多个,这里就懒得写了
public static void close(ResultSet rs,Statement st,Connection conn){
try {
if (rs!=null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (st!=null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.test.orm;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 使用Object[]来封装一条记录
* 使用List<Object[]>存储多条记录
*/
public class Demo01 {
public static void main(String[] args) {
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
List <Object[]> objsList = new ArrayList<Object[]>();
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id > ?");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
Object[] objs = new Object[3]; //一个Object数组封装了一条记录的信息!
objs[0] = rs.getString(1);
objs[1] = rs.getDouble(2);
objs[2] = rs.getInt(3);
objsList.add(objs);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
for (Object[] objs : objsList) {
System.out.println(objs[0]+"--"+objs[1]+"--"+objs[2]);
}
}
}
二、S03E278_01JDBC_ORM原理、Map封装一条记录、Map和List封装多条记录
package com.test.orm;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 使用Map存储一条记录中的多行
* 使用List<Map>存储各行中的多行
* 使用Map<Map>存储各行中的多行
*/
public class Demo02 {
/**
* 使用Map存储一条记录中的多行
*/
public static void test01(){
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
Map<String,Object> row = new HashMap<String,Object>(); //使用一个Map封装一条记录
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id = ?");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
row.put("empname", rs.getObject(1));
row.put("salary", rs.getObject(2));
row.put("age", rs.getObject(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
System.out.println("Map存储单行的多列:");
//遍历Map,就是遍历这一行的多列的信息
for (String key : row.keySet()) {
System.out.print(key+"--"+row.get(key)+"\t");
}
System.out.println();
}
/**
* 使用List<Map>存储各行中的多行
*/
public static void test02(){
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
//List封装
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id > ? order by empname");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
Map<String,Object> row = new HashMap<String,Object>(); //使用一个Map封装一条记录
row.put("empname", rs.getObject(1));
row.put("salary", rs.getObject(2));
row.put("age", rs.getObject(3));
list.add(row); //Map加入List
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
System.out.println("List存储各行的多列:");
//遍历list
for (Map<String, Object> map : list) {
//遍历Map,就是遍历这一行的多列的信息
for (String key : map.keySet()) {
System.out.print(key+"--"+map.get(key)+"\t");
}
System.out.println();
}
}
/**
* 使用Map<Map>存储各行中的多行
*/
public static void test03(){
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
//Map封装
Map<String,Map<String,Object>> maps = new HashMap<String,Map<String,Object>>();
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id > ? order by empname");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
Map<String,Object> row = new HashMap<String,Object>(); //使用一个Map封装一条记录
row.put("empname", rs.getObject(1));
row.put("salary", rs.getObject(2));
row.put("age", rs.getObject(3));
maps.put(rs.getString(1), row); //Map加入Map
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
System.out.println("Map存储各行的多列:");
//遍历Map
for (String empname : maps.keySet()) {
Map<String,Object> row = maps.get(empname);
//遍历Map,就是遍历这一行的多列的信息
for (String key : row.keySet()) {
System.out.print(key+"--"+row.get(key)+"\t");
}
System.out.println();
}
}
public static void main(String[] args) {
test01(); //单条
test02(); //多行
test03(); //多行
}
}
三、S03E279_01JDBC_ORM原理、使用javabean对象封装一条记录
package com.test.orm;
import java.sql.Date;
public class Emp { //表结构和类对应
private Integer id; //尽量用包装类
private String empname;
private Double salary;
private Date birthday;
private Integer age;
private Integer depId;
public Emp() {
}
public Emp(String empname, Double salary, Integer age) {
super();
this.empname = empname;
this.salary = salary;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmpname() {
return empname;
}
public void setEmpname(String empname) {
this.empname = empname;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getDepId() {
return depId;
}
public void setDepId(Integer depId) {
this.depId = depId;
}
}
package com.test.orm;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 使用Javabean对象存储一条记录中的多行
* 使用List<Javabean>存储各行中的多行
*/
public class Demo03 {
/**
* 使用Javabean对象存储一条记录中的多行
*/
public static void test01(){
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
Emp emp = null;
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id = ?");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
emp = new Emp(rs.getString(1), rs.getDouble(2), rs.getInt(3));
System.out.println("Javabean对象存储一条记录中的多行");
System.out.println(emp.getEmpname()+"--"+emp.getSalary()+"--"+emp.getAge());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
}
/**
* 使用List<Javabean>存储各行中的多行
*/
public static void test02(){
Connection conn = JDBCUtil.getMysqlConn();
PreparedStatement ps = null;
ResultSet rs = null;
//List封装
List<Emp> list = new ArrayList<Emp>();
try {
ps = conn.prepareStatement("select empname,salary,age from emp where id > ? order by empname");
ps.setObject(1, 1); //从1开始算参数
rs = ps.executeQuery();
while (rs.next()) {
Emp emp = new Emp(rs.getString(1), rs.getDouble(2), rs.getInt(3));
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(rs, ps, conn);
}
System.out.println("List存储各行的多列:");
//遍历list
for (Emp emp : list) {
System.out.println(emp.getEmpname()+"--"+emp.getSalary()+"--"+emp.getAge());
}
}
public static void main(String[] args) {
test01(); //单条
test02(); //多行
}
}