Atitit db query op shourt code lib list 数据库查询最佳实践 JdbcTemplate spring v2 u77 .docx Atitit db query o

Atitit db query op shourt code lib list 数据库查询最佳实践 JdbcTemplate spring v2 u77 .docx

Atitit db query op shourt code lib list 数据库查询类库最佳实践v2

 

目录

1. 查询结果映射规范 1

1.1. 1.常见的四种配置ResultSetHandler 1

1.2. ColumnListHandler 1

1.3. MapListHandler 1

1.4. MapHandler     map to a object map 1

1.5. ScalarHandler    map to a object value 1

1.6. 2

1.7. 离线数据api DataTable DataSet RowSet 2

2. spring-jdbc 2

2.1. Jpa 6

2.2. Dbutil 6

2.3. Mybtis trouble 7

2.4. Hb trb ，needcfg file 7

 

1. 查询结果映射规范

Atitit 数据库结果集映射  ResultSetHandler

 

目录

1. 常见的四种配置ResultSetHandler 1

2. Dbutil 1

3. Mybatis  致敬 3

4. Hbnt 3

5. Php 4

6. 、PDO常用方法及其应用 4

7. Ref 4

 

 

1. 1. 1.常见的四种配置ResultSetHandler
   2. ColumnListHandler
   3. MapListHandler   
   4. MapHandler     map to a object map
   5. ScalarHandler    map to a object value
   7. 离线数据api DataTable DataSet RowSet

ADO .NET最大的特性是对断开连接方式的全方位支持，其引入了DataSet、DataTable、DataRow等等对象，构建了一个“内存数据库”简化版本

DataAdapter把DataReader的数据填充到DataTable或者DataSet中，给使用者一个直观的使用方式

 

 

ADO .NET最大的优点是对断开连接访问数据库方式的强有力支持。相比起来，JDBC也引入类似的功能，RowSet，但是比起ADO .NET来，还是不够。

 

Php pdo好像没有离线数据api

 

1. spring-jdbc

 

JdbcTemplate主要提供以下五类方法：

•  

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；

•  
•  

update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；

•  
•  

query方法及queryForXXX方法：用于执行查询相关语句；

• queryForMap queryForObject queryForList queryForRowSet
•  

call方法：用于执行存储过程、函数相关语句。

•  

 

 

 

package db;

 

import java.util.List;

import java.util.Map;

 

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.EntityTransaction;

import javax.persistence.Persistence;

import javax.persistence.Query;

 

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

 

import com.google.common.collect.Maps;

 

public class SpringJdbcT {

 

public static void main(String[] args) {

 

Map properties = Maps.newLinkedHashMap();

properties.put("javax.persistence.jdbc.driver", "org.sqlite.JDBC");

 

properties.put("javax.persistence.jdbc.url", "jdbc:sqlite:test" + Math.random() + ".db");

 

System.out.println(properties);

// Create a new EntityManagerFactory using the System properties.

// The "hellojpa" name will be used to configure based on the

// corresponding name in the META-INF/persistence.xml file

// from hibernate-jpa-2.1-api jar

EntityManagerFactory factory = Persistence.createEntityManagerFactory("HbntUnit", properties);

 

exeUpdate(factory, "CREATE TABLE sys_data (jsonfld json  )");

exeUpdate(factory, "insert into sys_data values('{\"age\":88}')");

 

//jpa query err ,cant find entity map

// System.out.println(query(factory,"select json_extract(jsonfld,'$.age') as age from sys_data") ); ;

// System.out.println("f");

 

DriverManagerDataSource dataSource = new DriverManagerDataSource();

dataSource.setDriverClassName("org.sqlite.JDBC");

dataSource.setUrl(properties.get("javax.persistence.jdbc.url").toString());

//         dataSource.setUsername("guest_user");

//         dataSource.setPassword("guest_password");

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

String sql = "select json_extract(jsonfld,'$.age') as age from sys_data";

List li = jdbcTemplate.queryForList(sql);

System.out.println(li);

 

}

 

private static int exeUpdate(EntityManagerFactory factory, String sql) {

try {

EntityManager em = factory.createEntityManager();

EntityTransaction transaction = em.getTransaction();

transaction.begin();

/ sql

 

// sql = MessageFormat.format(sql, "'" + getUpflag() + "'", "'" + getUpflag() +

// "'", "'" + getUpflag() + "'");

System.out.println(sql);

Query createNativeQuery = em.createNativeQuery(sql);

int executeUpdate = createNativeQuery.executeUpdate();

System.out.println(executeUpdate);

 

transaction.commit();

return executeUpdate;

} catch (Exception e) {

e.printStackTrace();

}

return 0;

 

}

 

 

private static List<Map> query(EntityManagerFactory factory, String sql) {

EntityManager em = factory.createEntityManager();

Query createNativeQuery = em.createNativeQuery(sql ,Map.class );

 

// createNativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  

 List<Map> result = createNativeQuery.getResultList();

 return result;

}

}

 

 

dependencies {

 

// https://mvnrepository.com/artifact/org.springframework/spring-jdbc

compile group: 'org.springframework', name: 'spring-jdbc', version: '5.2.7.RELEASE'

 

 

// https://mvnrepository.com/artifact/org.springframework/spring-core

compile group: 'org.springframework', name: 'spring-core', version: '5.2.7.RELEASE'

 

 

// https://mvnrepository.com/artifact/org.eclipse.persistence/org.eclipse.persistence.jpa

compile group: 'org.eclipse.persistence', name: 'org.eclipse.persistence.jpa', version: '3.0.0-M1'

 

 

// https://mvnrepository.com/artifact/com.zsoltfabok/sqlite-dialect

compile group: 'com.zsoltfabok', name: 'sqlite-dialect', version: '1.0'

 

 

 // https://mvnrepository.com/artifact/com.alibaba/fastjson

compile group: 'com.alibaba', name: 'fastjson', version: '1.2.72'

 

 

// https://mvnrepository.com/artifact/org.hibernate.javax.persistence/hibernate-jpa-2.1-api

compile group: 'org.hibernate.javax.persistence', name: 'hibernate-jpa-2.1-api', version: '1.0.2.Final'

 

 

 

// https://mvnrepository.com/artifact/org.hibernate/hibernate-entitymanager

 compile group: 'org.hibernate', name: 'hibernate-entitymanager', version: '5.4.18.Final'

 

 

// https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind

compile group: 'com.fasterxml.jackson.core', name: 'jackson-databind', version: '2.11.1'

 

 

1. 1. Jpa

 

Lib rank is hb,eclipsejpa ,apache openjpa

Not compartitl....hb not query list map...

Eclipsesjpa cant use.

Openjpa apche ms not supt sqlite

 

1. 1. Dbutil

 

Class.forName("org.sqlite.JDBC");

 

Connection c = DriverManager.getConnection("jdbc:sqlite:test.db");

Statement stmt = c.createStatement();

 

String sql2 = "drop TABLE sys_data ";

exeUpdateSafe(stmt, sql2);

  sql2 = "CREATE TABLE sys_data (jsonfld json  )";

exeUpdateSafe(stmt, sql2);

 

 

// insert into facts values(json_object("mascot", "Our mascot is a dolphin name

// sakila"));

//

String sql = "insert into sys_data values('{\"id\":\"19\", \"name\":\"Lida\"}');";

exeUpdateSafe(stmt, sql);

 

 

sql="SELECT json_extract(jsonfld,'$.name') as name1 FROM sys_data limit 1;" ;

System.out.println(sql);

QueryRunner run = new QueryRunner();

//maphandler scare_handler

List<Map<String, Object>> query = run.query(c,sql, new MapListHandler());

System.out.println(query);

// run.query(conn, sql, rsh)

 

1. 1. Mybtis trouble
   2. Hb trb ，needcfg file