JDBC_SQL

JDBC配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/dangdang
jdbc.username=root
jdbc.password=root

oracle.driver=oracle.jdbc.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:xe
oracle.username=hr
oracle.password=hr

class.forName(oracle.jdbc.OracleDriver)
jdbc:oracle:thin:@localhost:1521:xe
jdbc:mysql://localhost:3306/student
JDBC内容
  • 接口:sun定义 java.sql 和javax.sql
  • 实现类:数据库厂商提供
jdbc使用
  • 注册驱动类
  • 创建链接
  • 创建statement(不常用)
  • 执行sql
  • 处理结果
  • 释放资源statement 也要close
  • get列时序号从1开始
prepareStatementjava.sql statement子接口
PrepareStatement ps =conn.prepareStatement(sql);
ps.setxxx(1,name)
ps.setxxx(2,password)
ps.exq exu (使用无参的构造)
// 里面的参数不用拼接
// 在执行前时插入
服务器操作

  • 检查权限,

  • 检查语法

  • sql转化为内部指令 前三是编译过程

  • 执行指令

    • statement创建成功时不发送sql,执行时发送

    • for(int i=0;i<10;i++{
			stat.ex(insert...)  
}
//        prep 可只把setxxx写在for循环中只用编译一次即可,所以说也叫作预编译语句

  • stat:异构sql

  • prep:同构sql，只有参数不同

日期转换

  • 日期转换 strDate --- util.Date---sql.Date

  • 插入或修改字段的类型是时间类型Date时，需要使用时间转换

    • 设置格式，格式应与字符串相匹配。和数据库中to_date方法类似

      SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

    • strDate--util.Date

      Date util
Date = format.parse(strDate);

    • util.Date---sql.Date

      java.sql.Date date = new java.sql.Date(utilDate.getTime());
事务控制
  • JDBC默认的是自动的事务提交
  • 相关API：控制事务提交的对象为连接 Connection
    • 设置事务提交方式为手动
    • conn.setAutoCommit(false)；//true---自动提交 false---手动提交
    • 事务提交：conn.commit();
    • 事务回滚：conn.rollback();
业务类方法的书写步骤
//1.获取Connection
    conn = JdbcUtil2.getConnection();
System.out.println("这是service获得连接："+conn);
//2.设置事务提交为手动提交
	conn.setAutoCommit(false);	
//3.调用DAO中的方法完成业务操作
	dao.insert(clazz);
//4.提交事务 or 回滚事务
	conn.commit();
conn.rollback
当前的事务控制存在问题
  • 首先service层获得的连接与Dao不是一条 其次通过单例可以解决这样的问题，但是会存在线程不安全的情况。
  • 为了解决线程安全，且可以控制事务的问题，我们需要使用线程局部表变量，ThreadLocal
线程局部变量
//线程局部变量，再多线程环境下，可以为每个使用该变量的线程 分配线程内部独有的 线程变量值。
ThreadLocal th = new ThreadLocal();
th.set（obj）;//往线程局部变量中设置	
obj = th.get();//返回该线程局变量的值
th.remove()；//移除该线程局变量的值
//作用：
//	1）保证 同一个线程内部 使用相同的值
//	2）保证不用线程使用不用的值

//原理：
//	当调用 set方法时，会将当前线程对象当做Key，将set中的参数当做value 放入到一个map集合中 ，这个集合由Thread进行管理的。
//当使用get方法时，以当前线程为key来向该map集合取值。
    
//使用ThreadLoacl来修改getConnection方法
	//创建连接的方法
private static ThreadLocal<Connection> th = new ThreadLocal<Connection>();
public static Connection getConnection(){
	Connection conn = th.get();
		if(conn==null){//如果为空就创建	
		//1.注册驱动	
			try {		
				Class.forName(p.getProperty("driver"));	
				//2.建立连接		
				conn = DriverManager.getConnection(p.getProperty("url"),p.getProperty("username"),p.getProperty("password"));	
			//3.并把连接放入ThreadLocal			
				th.set(conn);
				} catch (Exception e) {
					e.printStackTrace();
			}
		}
	return conn;
}

if(conn!=null){
try {		
	conn.close();
	th.remove();
} catch (SQLException e) {
				e.printStackTrace();
			}
		}
//注意：
//1)在控制事务时，dao内不要关闭连接
//2)在关闭连接后，要清空线程局部变量中的废连接
十、JDBC的

JdbcTemplate

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class JdbcTemplate<T> {
    /*
     * 专门完成增 删 改操作
     * 
     * insert into t_user (id,name,password) values (suns_seq.nextval,?,?);
     * 
     * jdbcTemplate.update(sql,new Object[]{"suns","123456"});
     * 
     * delete from t_user where id = ?
     * 
     * jdbcTemplate.delete(sql,new Object[]{1});
     * 
     * update t_product set name=?,price=? where id = ? 
     * 
     * jdbcTemplate.update(sql,new Object[]{"suns",10.2,1});
     * 
     * 可变长参数  1 等同于数组
     *          2 只能放在参数表最后 ，只能有一个
     *          
     *  "delete from t_user where id = 10";        
     * 
     */
	public void update(String sql,Object... args){
		Connection conn = null;
		PreparedStatement pstmt = null;
		try{
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			if(hasUpdateParam(args)){
			   for(int i=0;i<args.length;i++){
				 pstmt.setObject(i+1,args[i]);
			   }
			}   
			
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(null, pstmt);
		}
		
	}
	private boolean hasUpdateParam(Object... args) {
		return args.length!=0;
	}
	public List<T> query(String sql,RowMapper<T> rowMapper,Object... args){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<T> rets = new ArrayList<T>();
		try{
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			if(hasUpdateParam(args)){
   			  for(int i=0;i<args.length;i++){
				pstmt.setObject(i+1, args[i]);
			  }
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
			     T ret = rowMapper.mapRow(rs);
			     rets.add(ret);
			}
			return rets;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			JDBCUtil.close(null, pstmt, rs);
		}
	}
	public T queryForObject(String sql,RowMapper<T> rowMapper,Object... args){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		T ret = null;
		try{
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			if(hasUpdateParam(args)){
   			  for(int i=0;i<args.length;i++){
				pstmt.setObject(i+1, args[i]);
			  }
			}
			rs = pstmt.executeQuery();
			if(rs.next()){
			    ret = rowMapper.mapRow(rs);
			}
			return ret;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			JDBCUtil.close(null, pstmt, rs);
		}
	}
}

TransactionManager


import java.sql.Connection;
import java.sql.SQLException;
/*
 *  事务管理器 用于控制事务
 */
public class TransactionManager {

	public static void begin(){
		try {
			Connection conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void commit(){
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection();
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.close(conn, null);
		}
	}
	public static void rollback(){
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection();
			conn.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.close(conn, null);
		}
	}
}

JDBCUtil

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/*
 * 性能 效率 内存使用
 * 维护性 （便于修改）
 * 基本功能
 */
public class JDBCUtil {
	private static Properties p = new Properties();
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	
	static{
		//1 读入Properties文件
		  InputStream is = JDBCUtil.class.getResourceAsStream("/jdbc.properties");
		 //2 传入Properties集合
		  try {
			p.load(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//创建连接
	public static Connection getConnection() throws Exception{
		  Connection conn = tl.get();
		  if(conn==null){
			  Class.forName(p.getProperty("driver"));
			  conn = DriverManager.getConnection(p.getProperty("url"),p.getProperty("username"),p.getProperty("password"));
			  tl.set(conn);  
			  return conn;
		  }
		  return conn;
	}
	
	//关闭资源
	public static void close(Connection conn,PreparedStatement pstmt,ResultSet rs){
		if(rs!=null){try{rs.close();}catch(Exception e){}}
		if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
		if(conn!=null){try{conn.close();tl.remove();}catch(Exception e){}}
	}
	
	public static void close(Connection conn,PreparedStatement pstmt){
		if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
		if(conn!=null){try{conn.close();tl.remove();}catch(Exception e){}}
	}
	
}

SQL

select last_insert_id():函数,获取最后插入的id

<!--mybatis配置:-->
<insert id="save" useGeneratedKeys="true" >
		<!-- 插入后获取新生成id 放在user 的id :即java中生成user对象,此时是无id的,有此配置,插入后,获取id时不为空-->
		<selectKey keyProperty="t_id" resultType="Integer" order="AFTER">
			select last_insert_id()
		</selectKey>
		insert into t_user_d (t_name,t_password)values(#{t_name},#{t_password})
	</insert>
  • 备份数据库 mysqldump -u root -p --all-databases>./mysql.sql 备份到当前目录下,带数据备份
  • 添加列:alter table name add(列名,类型 默认值 约束,列名2 类型)
  • 删除列:alter table name drop (zuduan)
  • 修改列名alter table name rename old to new
  • 级联删除表 drop table name cascade constraint
  • 修改列的属性 alter table name modify(列名 类型 默认值....)
  • 删除约束 alter table name drop constraint 约束名;建表时可指定
    • 例:name char constraint1 primary key
  • 批量插入:insert into table() values(),(),();
  • 授权,并建用户grant all privileges on renzhiyuan.* to renzhiyuan@'%' identified by 'renzhiyuan';
  • 查看权限:show grants for renzhiyuan@'%'
单行函数
  • 作用于 表中的单行数据,每有一行数据执行该函数一次
  • Sysdata:获取系统当前时间
  • to_char(时间类型数据,'日期格式'):select to_cahr(sysdata,'yyyy-mm-dd') from mysql
分组

group by使用规则:一般分组后求组函数max,min,avg等

  • 只有在group by中出现的字段,才能写在select后
    • 例:select 籍贯 from student group by 籍贯;只显示能显示籍贯列,去重后显示
  • 在group by 中没有出现的字段,则配合组函数也可写在select中
  • 在group by中出现的单行函数,在select中可以出现,但必须保证单行函数必须完全相同
    组函数不能放在where中
Having

  • 作用:对于分组后结果进行过滤,符合条件留下

  • select 籍贯 from student group by 籍贯,having 过滤条件

  • 例:统计1997年个月份的员工人数,显示人数超过3人的月份

    • 筛选1997年入职的员工where to_char(hire_date,"yyyy")=1997

    • 进行分组group by to_cahr(hire_date,"mm")

    • select to_cahr(hire_date,"mm"),count(*) 
		from employee 
		where to_char(hire_date,"yyyy")=1997
		group by to_cahr(hire_date,"mm")
		having cout(*)>3//having只能在分组后使用

先找97年,之后按月份组,显示月份,及人数总数,最后having>3，也可以先月份分组,在havin中选97年 切count>3

where 与having区别
  • 功能上
    • where对原始表的数据(from后的表)进行过滤,having对分组后的数据进行过滤,
    • where是对于行数据的筛选,having是对于分组数据的的筛选
  • where和having在某些情况下可以显示相同的效果但where效率更高,参考sql执行顺序.
sql执行顺序

select...
from 表名
where 行数据的筛选条件
group by 分组依据
having by 分组数据的筛选条件
order by 排序依据
执行顺序:
1)from:确定原始表
2)where:对原始表的数据进行筛选,符合条件的留下
3)group by:对留下的数据基于分组条件进行分组
4)having:对分组后数据进行过滤
5)select:对于留下的数据进行字段筛选或计算等
6)order by:排序永远放在最后执行

伪列

oracle独有的pl-sql的内容

  • rowid

    • 根据数据在硬盘中存储的物理地址计算得来,
    • 作用:数据的默认索引,底层使用

  • rownum对查询结果进行编号,与where同时进行的

    • 按顺序对符合条件的数据进行编号
    • 例:查询工资前五的员工

  • *后面不能放字段,所以伪列放*前

    select * ,rowid from table //错
select rowid,*from table //对
select 别名.* ,rowid from table 别名//对
表链接

分类:内连接,外链接(左右全),交叉连接,自连接

  • 内连接

    select*from t1 inner join t2 on 连接条件 where ....
			slect 
			from employee  e1
			inner join part t2
			on e1.part_id = t2.part_id;

  • 左外连接

    • (顺序有要求,左表为主,左去右找,左边表连接右边表,左找不到右,右部分空)
    • left (outer可省) join

  • 右外连接

    • (右边为主,右找左,左边无,左则为空)
    • right (outer可省) join

  • 全外连接

    • full (outer可省) join
    • 正常的匹配,不正常的左边为空的显示,右边为空的也显示

  • 交叉连接

    • cross join
    • 表一的每条,与表二的所有连接,无条件连接,数量相乘

  • 自连接

    • 特殊链接 join

    • 查找课程表中的前驱课程

      select from  emp1 join emp2 on 1.名 =2.前取名

  • 多表连接的语法

    select ..from t1 join t2 on 链接条件 join t3 on 条件 join t4 on ..
约束

  • 主键,例:如学号,但若学生学号 用1,2,3,4,5, primary key

  • 唯一,表示该类内容不可重复,但可同时为空,unique

  • 非空 该列必须要内容 not null

  • 检查/自定义 条件筛选 check(表达式) 表达式和where基本一致

    • 性别检查:sex char(3) check(sex in('男','女'))
    • check(email like '%@%')

  • 外键,表示该列的内容必须在其他表的作主键/或唯一字段中出现

    • references table(主键/唯一
    • 外键不唯一,

  • 联合及约束(表级约束)

    • 如选课表:学生号,课程号(两个组合是唯一的)

    • 在一张表中任意一个字段无法表示一行数据时需要多个字段联合标识:成为联合主键约束

    • 联合主键约束,primary key(字段1,字段2)

    • 联合唯一约束,unique(字段1,字段2)

    • cerate table stu{
			sid varchar(2) references(...,
			cid varchar(2) ......,
			primary key(sid,cid)
		}
序列&视图&索引
  • 序列
    • 作用,用于自动按顺序生成的一组数字,通常作为oracle的一种主键生成簇
    • create Sequence 序列名 start with 100;从100开始每次曾一
    • create Sequence 序列名 start with 100 increment by 5;每次增5
    • 使用:序列名.nextval 获取序列中下一个有效值可作为一个自增变量使用(一个序列所有表共享)
    • insert into tname values(序列名.nxetval,"dzf")
    • 序列值一旦产生,不能重复,不回头
    • 命名规范:seq_使用表名
    • 删除:drop sequence ...
  • 视图
    • 作用:一个起了别名的复杂查询语句
    • 语法:create view as 查询语句(select ......)
    • 使用:select ...from view
    • 删除:drop view name;
    • 注意:简化sql语句,不提高效率
      • 不会添加任何字段标记,不影响数据的存贮空间
  • 索引
    • 作用:提高数据库的查询效率
    • create index name on tablename(字段名)
    • 使用:被添加索引列出现在where中 会自动使用该索引,(有的数据库要手动调用)
    • 删除:drop index name;
    • 注意:索引并不是创建的越多越好(会有字段标记)
      • 增删改数据时,不仅要维护原始表,还要维护索引,从而降低效率
      • 大量数据表中,查询少量内容时,建议使用索引
      • 主键与唯一列 默认提供索引

SQL优化避免select *

  • select age,name .... 固定长度属性在前

  • 内存代替表, 性别等

  • 读写分离,两个相同的数据库

  • 分库:表多的时候,放到不同数据库

  • 分表:水平(onetoone),垂直

  • 命中索引

  • 组合索引代替索引合并(多列但索引)

  • 尽量使用短索引 (不要对文章建立索引)

    • 如取一条数据时,使用limit 1
      select id,name from tb where name ="dzf"; 默认情况会通过逐行查询,或索引查询,找到后还会继续查询
      select id,name from tb where name ="dzf" limit 1,找到第一个就停

  • 使用join代替子查询

  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
    如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

  • 应尽量避免在 where 子句中使用！=或操作符，否则引擎将放弃使用索引而进行全表扫描。

  • 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，（可以使用union）

  • in 和 not in 也要慎用，否则会导致全表扫描（能用 between 就不要用 in）

  • 下面的查询也将导致全表扫描。
    select id from t where name like '%李%'，select id from t where name like '%李'
    若要提高效率，可以使用此格式select id from t where name like '李%'，也可以考虑全文检索。

  • 避免在索引列上使用计算，也就是说，应尽量避免在 where 子句中对字段进行表达式操作和函数操作，这将导致引擎放弃使用索引而进行全表扫描。
    如：select id from t where num/2=100应改为：select id from t where num=100*2

  • 很多时候用 exists 代替 in 是一个好的选择：exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。
    select num from a where num in(select num from b)
    用下面的语句替换：select num from a where exists (select 1 from b where num=a.num)

  • 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

  • 用>=替代>
    高效： SELECT * FROM EMP WHERE DEPTNO >=4
    低效： SELECT * FROM EMP WHERE DEPTNO >3
    两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

  • 用Where子句替换having子句

创建索引，但无法命中索引，参考https://www.cnblogs.com/wupeiqi/articles/5716963.html
select * from tb1 where name='alex';
like '%xx'
select * from tb1 where name like '%cn';
使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
!=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
>
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
posted @ 2019-12-04 23:42  下雨天,真好  阅读(185)  评论(0)  编辑  收藏  举报