java使用jdbi连接几种数据库的方法

暂时记录三种工作中用到的数据库的连接方式,以后有需要再添加

1、Oracle & Mysql

驱动

<!-- oracle -->
<dependency>
   <groupId>com.oracle</groupId>
   <artifactId>ojdbc6</artifactId>
   <version>12.1.0.1-atlassian-hosted</version>
</dependency>

<!-- mysql -->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.6</version>
</dependency>

 

 

public static void main(String[] args) {
    DBI dbi = null;
    Handle h = null;
    username = "test";
    List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
    dbi = new DBI("jdbc:oracle:thin:@" + dbHostPort, dbUsername, dbPassword);

　　　//dbi = new DBI("jdbc:mysql://" + dbHostPort, dbUsername, dbPassword);

h = dbi.open(); 
result = h.createQuery("select id,name,age from usertable where name = :username").bind("username", username).list(); 

for (Map<String, Object> map : result) { 
System.out.println(map.get("id").toString() + " 姓名： "+ map.get("name").toString() + " 年龄：" + map.get("age").toString();
 } 
}

2、Db2

需要驱动 ：db2jcc

 public static void main(String[] args) {
    DBI dbi = null;
    Handle h = null;
    try {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        try {
　　　　　　　//这里需要指定currentSchema
            dbi = new DBI("jdbc:db2://192.168.1.195:50500/acs:currentSchema=ACS;", dbUsername, dbPassword);
            h = dbi.open();
            int rowsNumber = h.createStatement("update usertable t set name = 'DDDD' where t.id = 13").execute();    
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(h!=null){
                h.close();
            }
        }            
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

3、sqlite

驱动 ： sqlitejdbc-v033-nested.jar

public static void main(String[] args) {
    DBI dbi = null;
    Handle h = null;
    String sqlurl="jdbc:sqlite:" + db文件名;
    try {
        Class.forName("org.sqlite.JDBC");
        dbi = new DBI(sqlurl);
        h = dbi.open();
        Map<String, Object> ret = h.createQuery("select * from usertable").first();
        System.out.println(ret.get("name").toString());
    } catch (Exception e) {
        e.printStackTrace();
    }
}