首页 > 代码库 > 数据库去O整理
数据库去O整理
一、oracle递归查询语句start with ...connect by prior
① 给你一张表，表里面有主键id,以及该项的父节点parent_id,查询出该表中所有的父子关系节点树？
Oracle: start with ...connect by prior
例如：对分类下的所有组图(包括子分类下的组图)列表进行分页
select g.* from t_group g, t_counter c where g.counter_id = c.id and g.category_id in ( select id from t_category start with id = ? connect by prior id = parent_id and status = 1) and status = 1 order by c.pv desc
mysql:没有oracle那种自带的查询参数，在java端写递归函数，或者递归的存储过程
递归查找某一分类下的所有子分类,数据表结构如图
CREATE TABLE t_category ( category_id int(20) unsigned NOT NULL COMMENT ‘栏目id，主键‘, parent_id int(20) unsigned COMMENT ‘分类父类id‘, original_parent_id int(20) unsigned COMMENT ‘原始父类id，放到垃圾箱保留原有从属关系‘, name varchar(255) COMMENT ‘名称‘, code varchar(100) COMMENT ‘编码‘, ... status int(2) COMMENT ‘状态；0： 草稿, 1： 通过（发布）, -1： 删除‘, ...)
java解决方案:
public void getAllChildren(long categoryId, int status, List<Long> categoryIdList) { List<Long> childrenIds = getCategoryChildrenIds(categoryId, status); for (long cateId : childrenIds) { categoryIdList.add(cateId); int count = geliDao.count("select count(1) from t_category where parent_id = ? and status = ?", cateId, status); if (count > 0) { getAllChildren(cateId, status, categoryIdList); } } }
在要使用该递归函数的地方，该这样使用
public Pager<Group> findGroupByCateAndName(long categoryId, String name, int pageNo, int pageSize) { SqlBuilder sqlBuilder = new SqlBuilder(); sqlBuilder.appendSql("select group_id from t_group where category_id in "); List<Long> routeIds = new ArrayList<Long>(); routeIds.add(categoryId); getAllChildren(categoryId, Category.STATUS_NORMAL, routeIds); sqlBuilder.appendValues(routeIds.toArray()); if(StringUtils.isNotBlank(name)) { sqlBuilder.appendSql(" and name like "); sqlBuilder.appendValue("%" + name + "%"); } sqlBuilder.appendSql(" order by group_id desc"); LOG.debug("findGroupByCateAndName : {}; {}", sqlBuilder.getSql(), sqlBuilder.getValues()); return new Pager<Group>(sqlBuilder.getSqlExt(), sqlBuilder.getValuesExt(), pageNo, pageSize); }二、oracle函数decode处理
在Oracle/PLSQL中, decode 具有和 IF-THEN-ELSE 一样的功能。
decode 函数语法如下：
decode( expression , search , result [, search , result]... [, default] )
expression 要比较的表达式.
search 要与expression 比较的字段。.
result 如果expression 与search 一样的话，返回该结果。.
default 此参数可选，如果没有与expression 匹配上的search . 就返回此结果，如果此参数没有设置，当没有与expression匹配上的search时，返回null。
search 和 result可成对出现多次，代表各种要匹配的情况。
sign(number) 函数返回一个数字的正负标志.
问题1: 现在一个阅读者想问，怎么使用decode函数来比较两个日期呢？（例如：date1 和 date2）, 如果date1 > date2, decode 函数返回date2. 否则decode函数返回 date1.
可用decode函数绑定SIGN 函数 像下面这样:
上面比较日期的语句可修改如下:
DECODE(SIGN(date1-date2), 1, date2, date1)
eg：如果有app参数就查询该app对应的指令数目,否则查询所有指令数目
public long searchCommandCount(String app) { if(app == null) app=""; String sql = "select count(0) "+ "from t_command a,t_application b where a.applicationid=b.id and b.application = decode(?,‘‘,b.application,?) "; return simpleJdbcTemplate.queryForLong(sql, app, app); }mysql处理后
public long searchCommandCount(String app) { if(app == null) app=""; StringBuilder sb = new StringBuilder(); sb.append("select count(0) from t_command a,t_application b where a.applicationid=b.id"); if(!"".equals(app)){ sb.append(" and b.application = "+ app); } /*String sql = "select count(0) "+ "from t_command a,t_application b where a.applicationid=b.id and b.application = decode(?,‘‘,b.application,?) ";*/ return simpleJdbcTemplate.queryForLong(sb.toString()); }
三、oracle自增长序列处理
关于SEQUENCE 的基本知识请参考ORACLE SEQUENCE用法
原处理方案：
CREATE SEQUENCE SEQ_TONY_APK_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1976634 CACHE 20 NOORDER NOCYCLE ;
//获取idpublic long createTonyId(){ return simpleJdbcTemplate.queryForLong("select seq_tony_apk_id.nextval from dual");}mysql解决方案：
普通的可能会想到用mysql的自增长auto_increament,不过这个在数据库做分库分表的时候，有可能出问题，具体原因请参照数据库分库分表(sharding)系列(二) 全局主键生成策略
使用全局主键表
CREATE TABLE gl_keygen( table_name varchar(255) NOT NULL COMMENT ‘表名，主键‘, last_used_id int(20) unsigned NOT NULL COMMENT ‘最后使用的id‘ ) ENGINE= InnoDB DEFAULT CHARSET=gbk;
我们使用一个Java类来控制某一字段自增长
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Map;import javax.sql.DataSource;public class IdTableGenerator { DataSource idGenDataSource; public void setIdDataSource(DataSource idGenDataSource) { this.idGenDataSource = idGenDataSource; } int size = 10; Map<String, IdHolder> holderMap = new java.util.concurrent.ConcurrentHashMap<String, IdHolder>(); public long generate(String tableName, String columnName) { IdHolder holder = holderMap.get(tableName); if (holder == null) { holder = new IdHolder(); holderMap.put(tableName, holder); } synchronized (holder) { if (holder.needAlloc()) { long lastUsedId = alloc(tableName, columnName, size); holder.currentId = lastUsedId + 1; holder.limit = lastUsedId + size; } else { holder.currentId ++; } return holder.currentId; } } static class IdHolder { long currentId; long limit; boolean needAlloc() {return currentId >= limit; } } public long alloc(String tableName, String columnName, int size) { long result = 0; Connection con = null; boolean oldAutoCommit = false; try { con = idGenDataSource.getConnection(); oldAutoCommit = con.getAutoCommit(); con.setAutoCommit(false); int updateCount = updateLastUsedId(con, tableName, columnName, size); if (updateCount == 0) { initIdTable(con, tableName, columnName); } result = getLastUsedId(con, tableName, columnName); con.commit(); } catch (Exception e) { try { con.rollback(); } catch (Exception ex) { ex.printStackTrace(); } throw new RuntimeException(e); } finally { if (con != null) { try { con.setAutoCommit(oldAutoCommit); con.close(); } catch (Exception ex) { ex.printStackTrace(); } } } return result; } static long getLastUsedId(Connection con, String tableName, String columnName) throws SQLException { PreparedStatement ps = con.prepareStatement("select LAST_USED_ID from GL_KEYGEN where table_name = ?"); ps.setString(1, tableName); ResultSet rs = ps.executeQuery(); rs.next(); long result = rs.getLong(1); rs.close(); ps.close(); return result; } static int updateLastUsedId(Connection con, String tableName, String columnName, int size) throws SQLException { PreparedStatement ps = con.prepareStatement("update GL_KEYGEN set last_used_id = last_used_id + ?" + " where table_name = ?"); ps.setInt(1, size); ps.setString(2, tableName); int result = ps.executeUpdate(); ps.close(); return result; } static void initIdTable(Connection con, String tableName, String columnName) throws SQLException { PreparedStatement ps = con.prepareStatement("select max(" + columnName + ") from " + tableName); ResultSet rs = ps.executeQuery(); rs.next(); long maxId = rs.getLong(1); rs.close(); ps.close(); ps = con.prepareStatement("insert into GL_KEYGEN (table_name, last_used_id) values (?, ?)"); ps.setString(1, tableName); ps.setLong(2, maxId); ps.executeUpdate(); ps.close(); }}<jee:jndi-lookup id="dataSource" jndi-name="jdbc/test"/><bean id="idGenerator" class="cn.tony.repository.IdTableGenerator" p:idDataSource-ref="dataSource"/>数据源配置
<database> <jndi-name>jdbc/test</jndi-name> <driver type="com.mysql.jdbc.Driver"> <url>jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=GBK&zeroDateTimeBehavior=convertToNull </url> <user>root</user> <password>root</password> </driver> <prepared-statement-cache-size>8</prepared-statement-cache-size> <max-connections>20</max-connections> <max-idle-time>30s</max-idle-time> </database>主键自增生成器使用案例：
public abstract class AbstractRepository <T> { @Autowired IdTableGenerator idGenerator; protected long getNextId(String tableName, String columnName){ return idGenerator.generate(tableName, columnName); } //默认使用id做主键 protected long getNextId(String tableName){ return idGenerator.generate(tableName, "id"); }}public class TonyRepository extends AbstractRepository<Tony> { public long createTonyId(){ return getNextId(Tony.TABLE_NAME); }}
四、其他函数
to_date---> str_to_date
(yyyy-MM-dd HH24:mi:ss) --- (%Y-%m-%d %H:%i:%s)
sysdate --->sysdate()
to_char --->mysql中没有对应的函数，MySQL必要时自动变换数字为字符串
mysql数据类型和长度
mysql中key 、primary key 、unique key 与index区别
Oracle的to_date函数
五、SQL中特殊符号处理
单引号处理
用字符串拼接的话，单引号必须经过判断并替换，在数据库中，用2个单引号代表1个实际的单引号。所以，如果是拼接方式，需要用String.Replace("’", "”")来替换一下，将1个单引号替换为2个就没有问题了。在模糊查询中,为了避免单引号，我们使用参数的方式，下面的语句是不对的：
SELECT * FROM yourTable WHERE name LIKE ‘%?%’;在这个句子中，’%?%’被整体当作一个字符串来处理，你无论如何查询不到结果。修改一下，SELECT * FROM yourTable WHERE name LIKE ?;然后添加参数的时候这么添加：
new Parameter("?", "%" + categoryName + "%");
通配符_ % 处理
如果用户输入的查询条件中含有通配符，必须将这些字符作为数据而不是通配符来对待
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");
左方括号([)问题
如果用户输入的查询参数本身就包括方括号时，会出现什么结果呢？
根据用户的期望，如果输入一个方括号，查询结果中应该只包括那些字段值中含有方括号的记录。
但是实验结果表明，如果是没有配成对的单个左方括号，查询时这个左方括号会被忽略。
也就是说，下面这个语句：
WHERE T2.name like (%+ [ + %)
等价于下面这个语句：
WHERE T2.name like (%+ + %)
这将导致查询结果中包含表中的全部记录，就像没有任何过滤条件一样。
为此，如果用户输入的查询条件中含有左方括号的话，还必须对左方括号进行转义：
s = s.Replace("[", "[[]");
注：右方括号没有这个问题。
结论
为了防止SQL注入，同时避免用户输入特殊字符时查询结果不准确的问题，应该做两件事：
（1）使用参数化查询。
（2）在使用用户输入的字符串数据设置查询参数值之前，首先调用下面的共通处理函数：
private static string ConvertSql(string sql) {
sql = sql.Replace("[", "[[]"); // 这句话一定要在下面两个语句之前，否则作为转义符的方括号会被当作数据被再次处理
sql = sql.Replace("_", "[_]").Replace("%", "[%]");
return sql;
}
数据库去O整理