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.常见的四种配置ResultSetHandler 1
1.4. MapHandler map to a object map 1
1.5. ScalarHandler map to a object value 1
1.7. 离线数据api DataTable DataSet RowSet 2
Atitit 数据库结果集映射 ResultSetHandler
目录
1. 常见的四种配置ResultSetHandler 1
2. Dbutil 1
3. Mybatis 致敬 3
4. Hbnt 3
5. Php 4
6. 、PDO常用方法及其应用 4
7. Ref 4
ADO .NET最大的特性是对断开连接方式的全方位支持，其引入了DataSet、DataTable、DataRow等等对象，构建了一个“内存数据库”简化版本
DataAdapter把DataReader的数据填充到DataTable或者DataSet中，给使用者一个直观的使用方式
ADO .NET最大的优点是对断开连接访问数据库方式的强有力支持。相比起来，JDBC也引入类似的功能，RowSet，但是比起ADO .NET来，还是不够。
Php pdo好像没有离线数据api
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'
Lib rank is hb,eclipsejpa ,apache openjpa
Not compartitl....hb not query list map...
Eclipsesjpa cant use.
Openjpa apche ms not supt sqlite
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)