7.2 dble连接Demo
开发框架连接
ibatis
利用ibatis连接dble时，连接方式与MySQL相同。下面是一个简单示例。 JDBC配置：jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc: mysql://127.0.0.1:8066/TESTDB?useUnicode=true&characterEncoding=utf-8 jdbc.user=root jdbc.password=123456映射文件配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.UserMapper">
<insert id="saveUser" parameterType="com.bean.User">
insert into user(id,name,phone,birthday)
values (0,#{name},#{phone},#{birthday})
<selectKey keyProperty="id" order="after" resultType="int">
select last_insert_id() as id
</selectKey>
</insert>
<delete id="deleteUserById" parameterType="java.lang.String">
delete from user where id=#{id}
</delete>
<update id="updateUser" parameterType="com.bean.User">
update user set name=#{name},phone=#{phone},birthday=#{birthday} where id=#{id}
</update>
<update id="updateUsers">
/\*!dble:sql=select * from user;\*/update users set usercount=(select count(\*) from user),ts=now()
</update>
<select id="getUserById" parameterType="java.lang.String" resultType="com.bean.User">
select \* from user where id=#{id}
</select>
<select id="getUsers" resultType="com.bean.User">
select \* from user
</select>
语句select last_insert_id() as id可用来获取新写入记录的ID。
updateUsers方法用到了 dble的注解，由于ibatis中的符号#具有特殊含义，因此注解中不能含有#。
hibernate
利用hibernate连接dble时，连接方式与MySQL相同。下面是一个简单示例。 hibernate.cfg.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc: mysql://192.168.58.51:8066/testdb?useUnicode=true&characterEncoding=utf-8</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">123456</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.format_sql">true</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<mapping resource="com/actiontech/test/News.hbm.xml"/>
</session-factory>
</hibernate-configuration>
News.hbm.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.actiontech.test.News" table="news_table">
<id name="id" type="java.lang.Integer">
<column name="id" />
</id>
<property name="title" type="java.lang.String">
<column name="title" />
</property>
<property name="content" type="java.lang.String">
<column name="content" />
</property>
</class>
</hibernate-mapping>
News.java：
package com.actiontech.test;
public class News {
private Integer id;
private String title;
private String content;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() { return content; } public void setContent(String content) { this.content = content; } }</pre> NewsManager.java：
package com.actiontech.test;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class NewsManager {
public static void main(String[] args)
throws Exception {
Configuration config = new Configuration().configure();
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
Transaction transaction = session.beginTransaction();
News news = new News();
news.setId(10);
news.setTitle("dble示例");
news.setContent("Hibernate 连接dble的第一个例子");
session.save(news);
transaction.commit();
session.close();
factory.close();
}
}
dble虽然支持Hibernate但不建议使用Hibernate，因为Hibernate无法控制SQL的生成，无法做到对查询SQL的优化，大数量下可能会出现性能问题。
JDBC
利用JDBC连接dble时，连接方式与MySQL相同。下面是一个简单示例：package com.actiontech.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicLong;
public class SingleMixEngine {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "123456");
SingleMixEngine engine = new SingleMixEngine();
engine.execute(props,"jdbc:mysql://192.168.58.51:8066/testdb");
}
final AtomicLong tmAl = new AtomicLong();
final String tableName="news_table";
public void execute(Properties props,String url) {
CountDownLatch cdl = new CountDownLatch(1);
long start = System.currentTimeMillis();
for (int i = 0; i < 1; i++) {
TestThread insertThread = new TestThread(props,cdl, url);
Thread t = new Thread(insertThread);
t.start();
System.out.println("Test start");
}
try {
cdl.await();
long end = System.currentTimeMillis();
System.out.println("Test end,total cost:" + (end-start) + "ms");
} catch (Exception e) {
}
}
class TestThread implements Runnable {
Properties props;
private CountDownLatch countDownLatch;
String url;
public TestThread(Properties props,CountDownLatch cdl,String url) {
this.props = props;
this.countDownLatch = cdl;
this.url = url;
}
public void run() {
Connection connection = null;
PreparedStatement ps = null;
Statement st = null;
try {
connection = DriverManager.getConnection(url,props);
connection.setAutoCommit(true);
st = connection.createStatement();
String dropSql = "drop table if exists " + tableName;
System.out.println("Execute SQL:\n\t"+dropSql);
st.execute(dropSql);
String createSql = "create table " + tableName + "(id int,title varchar(20),content varchar(50))";
System.out.println("Execute SQL:\n\t"+createSql);
st.execute(createSql);
String insertSql = "insert into " + tableName + " (id,title,content) values(?,?,?)";
System.out.println("Prepared SQL:\n\t"+insertSql);
ps = connection.prepareStatement(insertSql);
for (int i = 1; i <= 3; i++) {
ps.setInt(1,i);
ps.setString(2, "测试"+i);
ps.setString(3, "这是第"+i+"条测试数据");
ps.execute();
System.out.println("Insert data:\t"+i+","+"测试"+i+","+"这是第"+i+"条测试数据");
}
String querySQL = "select * from " + tableName + " order by id";
System.out.println("Execute SQL:\n\t"+querySQL);
ResultSet rs = st.executeQuery(querySQL);
int colcount = rs.getMetaData().getColumnCount();
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
String updateSql = "update " + tableName + " set title='test1' where id=1";
System.out.println("Execute SQL:\n\t"+updateSql);
st.execute(updateSql);
rs = st.executeQuery(querySQL);
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
String deleteSql = "delete from " + tableName + " where id=2";
System.out.println("Execute SQL:\n\t"+deleteSql);
st.execute(deleteSql);
rs = st.executeQuery(querySQL);
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
String createIndexSql = "create index idx_1 on " + tableName + "(title)";
System.out.println("Execute SQL:\n\t"+createIndexSql);
st.execute(createIndexSql);
String dropIndexSql = "drop index idx_1 on " + tableName;
System.out.println("Execute SQL:\n\t"+dropIndexSql);
st.execute(dropIndexSql);
} catch (Exception e) {
System.out.println(new java.util.Date().toString());
e.printStackTrace();
} finally {
if (ps != null)
try {
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
if (connection != null)
try {
connection.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
this.countDownLatch.countDown();
}
}
}
}