JDBC基本流程: 把oracle实现jar包拿到项目下 add as lib.. 1.加载驱动 (选择数据库) 2.建立连接 Connection (与数据库之间建立连接) 3.准备sql 4.封装处理块,发送sql 5.得到结果集 6.处理结果 7.关闭资源 public class Class001_JDBC { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 (选择数据库) Class.forName("oracle.jdbc.driver.OracleDriver"); //2.建立连接 Connection (与数据库之间建立连接) Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:XE", "scott", "TIGER" ); //3.准备sql String sql = "select * from dept"; //4.封装处理块 Statement state = conn.createStatement(); //5.发送sql,得到结果集 ResultSet reault = state.executeQuery(sql); //6.处理结果 while(reault.next()){ int deptno = reault.getInt(1); String dname = reault.getString(2); String loc = reault.getString(3); System.out.println(deptno+"--->"+dname+"--->"+loc); } //7.关闭资源 reault.close(); state.close(); conn.close(); } }
优化:
1.异常 捕获
2.通过配置文件实现软编码
public class Class002_JDBC {
public static void main(String[] args){
//构建 properties对象
Properties pro = new Properties();
try {
pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
//1.加载驱动 (选择数据库)
try {
Class.forName(pro.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.建立连接 Connection (与数据库之间建立连接)
Connection conn = null;
Statement state = null;
ResultSet result = null;
try {
conn = DriverManager.getConnection(
pro.getProperty("url"),
pro.getProperty("username"),
pro.getProperty("password")
);
//3.准备sql
String sql = "select * from dept";
//4.封装处理块
state = conn.createStatement();
//5.发送sql,得到结果集
result = state.executeQuery(sql);
//6.处理结果
while(result.next()){
//字段序号从1开始,每次+1
int deptno = result.getInt(1);
String dname = result.getString(2);
String loc = result.getString(3);
System.out.println(deptno+"--->"+dname+"--->"+loc);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//7.关闭资源
if(result!= null){
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(state!=null){
try {
state.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
/*
JDBC工具类
1.加载驱动
2.获取连接
3.关闭资源
练习 : 连接jdbc的流程进行简单的封装
*/
public class DBUtils {
private static Properties pro = new Properties();
static{
//1.加载驱动
//构建 properties对象
try {
pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
//加载驱动 (选择数据库)
try {
Class.forName(pro.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = DriverManager.getConnection(
pro.getProperty("url"),
pro.getProperty("username"),
pro.getProperty("password")
);
return conn;
}
//3.关闭资源
public static void close(ResultSet result, Statement state,Connection conn){
if(result!= null){
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(state!=null){
try {
state.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Statement state,Connection conn){
close(null,state,conn);
}
}
/*
对用户进行操作
1.注册用户
2.登录用户
3.修改用户信息
4.注销用户
预处理块的优点 :
1.防止sql注入
2.预先编译,可以提高效率
推荐使用预处理块代替静态处理块
注意:
在java中操作数据库中修改数据的时候,会自动提交事务
*/
public class Class003_User {
public static void main(String[] args){
System.out.println(update("zhangsan","321321"));;
}
//修改 根据用户名修改用户密码
public static boolean update(String username,String password){
//1.获取连接
Connection conn = null;;
PreparedStatement ps = null;
boolean flag = false;
try {
conn = DBUtils.getConnection();
//设置手动提交
conn.setAutoCommit(false);
//2.构建预处理块
ps = conn.prepareStatement("update t_user set password=? where username=?");
//3.为?赋值
ps.setObject(1,password);
ps.setObject(2,username);
//4.执行,得到影响行数
int rows = ps.executeUpdate();
//5.判断
if(rows>0){
flag = true;
conn.commit(); //提交
}else{
conn.rollback(); //回滚
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DBUtils.close(ps,conn);
}
return flag;
}
//登录 : 1)根据用户名与密码一起到数据库中查询,查询到了数据登录成功,否则登录失败 2)根据用于名去查询,得到结果的密码值与用户输入的密码比较,相等登录,不等登录失败
public static boolean login(String username,String password){
//1.获取连接
Connection conn = null;
PreparedStatement state = null;
ResultSet result = null;
try {
conn = DBUtils.getConnection();
//2.准备sql
String sql = "select * from t_user where username=? and password=?";
//3.构建预处理快
state = conn.prepareStatement(sql);
//4.需要为sql中的?占位符传递参数
state.setObject(1,username);
state.setObject(2,password);
//5.执行sql,得到结果集
result = state.executeQuery(); //预处理块新增的方法 executeQuery() executeUpdate()
//5.处理结果
if(result.next()){
return true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DBUtils.close(result,state,conn);
}
return false;
}
//注销
//注册用户
public static boolean reg(String username,String password){
//1.获取连接
Connection conn = null;
Statement state = null;
try {
conn = DBUtils.getConnection();
//2.准备sql
String sql = "insert into t_user values('"+username+"',"+password+")";
//3.构建处理快
state = conn.createStatement();
//4.执行sql,得到结果
int rows = state.executeUpdate(sql);
if(rows<=0){
return false;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DBUtils.close(state,conn);
}
return true;
}
}
/* 数据库通用访问对象封装 BaseDao -->扩展 增删改 查询 可变参数: ...表示可变参数 对应类型的形参个数有0~n个 方法的形参列表的最后存在 方法内部通过使用数组的方式使用可变参数接收的数据 注意: 在oracle中的number类型在java中默认转为java.math.BigDecimal */ public class BaseDao<T> { public List<T> testQuery(String sql,Class<T> cls,Object ...args){ //1.获取连接 Connection conn = null; PreparedStatement ps = null; ResultSet result= null; ResultSetMetaData data = null; List<T> list = new ArrayList<>(); //存储查询到的对象信息 try { conn = DBUtils.getConnection(); //2.构建预处理块 ps = conn.prepareStatement(sql); //3.为?赋值 if(args!=null && args.length!=0){ for(int i=0;i<=args.length-1;i++){ ps.setObject(i+1,args[i]); } } //4.执行sql,得到相应行数 result = ps.executeQuery(); //结果集原信息对象 data = result.getMetaData(); //从结果集原信息对象上获取当前结果集中每条数据的字段个数 int columnCount = data.getColumnCount(); //5.处理数据 //循环遍历结果集 while(result.next()){ //查询出一条数据,对应创建java中的一个对象 T obj = cls.newInstance(); //循环获取每一个列的值,获取每一个属性,为对象属性赋值 for(int i=1;i<=columnCount;i++){ //获取每一条数据的每一个字段的值 Object value = result.getObject(i); //判断value是否指向一个java.math.BigDecimal类型的对象,转为对应的int if(value instanceof BigDecimal){ BigDecimal b = (BigDecimal)value; value = b.intValue(); } //获取字段的名字 String columnName = data.getColumnLabel(i); //获取与字段所对应的属性 Field field = cls.getDeclaredField(columnName); //为当前创建的对象的这个属性赋值 //忽略权限 field.setAccessible(true); field.set(obj,value); } //把对象放入集合 list.add(obj); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (NoSuchFieldException e) { e.printStackTrace(); } finally { DBUtils.close(ps,conn); } return list; } /** * 增删改 * @param sql 要执行的sql语句 * @param args 为?赋值的实参 * @return 成功与否 */ public boolean update(String sql,Object[] args){ //1.获取连接 Connection conn = null; PreparedStatement ps = null; boolean flag = false; try { conn = DBUtils.getConnection(); //2.构建预处理块 ps = conn.prepareStatement(sql); //3.为?赋值 if(args!=null && args.length!=0){ for(int i=0;i<=args.length-1;i++){ ps.setObject(i+1,args[i]); } } //4.执行sql,得到相应行数 int rows = ps.executeUpdate(); //5.对相应行数判断结果 if(rows>0){ flag = true; } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { DBUtils.close(ps,conn); } return flag; } }
测试类 public class Class001_testUpdate { public static void main(String[] args) { BaseDao base = new BaseDao(); //System.out.println(base.update("insert into t_user values(?,?)",new Object[]{"wangwu",123})?"更新成功":"更新失败");; System.out.println(base.update("delete from t_user where username=?",new Object[]{"wangwu"})?"更新成功":"更新失败");; } }
package com.yjxxt.entity; import java.util.Objects; public class Dept { private int deptno; private String dname; private String loc; public Dept() { } public Dept(int deptno, String dname, String loc) { this.deptno = deptno; this.dname = dname; this.loc = loc; } public int getDeptno() { return deptno; } public void setDeptno(int deptno) { this.deptno = deptno; } public String getDname() { return dname; } public void setDname(String dname) { this.dname = dname; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Dept dept = (Dept) o; return deptno == dept.deptno && Objects.equals(dname, dept.dname) && Objects.equals(loc, dept.loc); } @Override public int hashCode() { return Objects.hash(deptno, dname, loc); } @Override public String toString() { return "Dept{" + "deptno=" + deptno + ", dname='" + dname + '\'' + ", loc='" + loc + '\'' + '}'; } }
public class Class001_testQuery { public static void main(String[] args) { BaseDao<Dept> base = new BaseDao<>(); List<Dept> list = base.testQuery("select deptno \"deptno\",dname \"dname\",loc \"loc\" from dept",Dept.class); System.out.println(list); } }