以mysql和oracle数据库为例;
我项目以mysql为主。但需要去一个oracle数据库里查询数据,所以只有mysql里表的实体类,但没有oracle数据库实体类,所以配置mysql的数据源有实体类直接把数据源放到session工厂里用hibernate和数据库交互(session工厂是hibernate特有的,但hibernate是通过实体类的映射和数据库联系的),oracle数据源我选择用spring的
tomcat里context.xml配置文件里:
WEB-INF/web.xml
name="jdbc/ims4"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
maxPoolSize="100"
minPoolSize="50"
acquireIncrement="5"
maxIdleTime = "1800"
acquireRetryAttempts = "100"
acquireRetryDelay = "1000"
testConnectionOnCheckin = "true"
automaticTestTable = "c3p0TestTable"
idleConnectionTestPeriod = "18000"
checkoutTimeout="3000"
factory="org.apache.naming.factory.BeanFactory"
user="xxx"
password="xxx"
driverClass="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://ip:端口/数据库?characterEncoding=utf8&autoReconnect=true"
/>
name="jdbc/oracle"
auth="Container"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
idleConnectionTestPeriod="60"
maxPoolSize="50"
minPoolSize="2"
acquireIncrement="2"
user="xxx"
password="xxx"
driverClass="oracle.jdbc.driver.OracleDriver"
jdbcUrl="jdbc:oracle:thin:@ip:端口:数据库"
/>
项目里接收:db.xml
java:comp/env/jdbc/ims4
java:comp/env/jdbc/oracle
mysql数据源直接用hibernate实现就可以(省略)
oracle数据源:
public static JdbcTemplate getJdbcTemplate() {
JdbcTemplate jdbcTemplate=(JdbcTemplate) getApplicationContext().getBean("jdbcTemplate");
return jdbcTemplate;
}
JdbcTemplate 的 queryForMap(sql)和queryForInt(sql);方法必须要有返回值,包装一下如下:
public static Map getJdbcTemplateMap(String sql){
try{
return getJdbcTemplate().queryForMap(sql);
}catch (EmptyResultDataAccessException e) {
return null;
}
}
附赠其他数据库的数据源配置:
2
3
5 auth="Container"
6 factory="org.apache.naming.factory.BeanFactory"
7 type="com.mchange.v2.c3p0.ComboPooledDataSource"
8 driverClass="com.mysql.jdbc.Driver"
9 idleConnectionTestPeriod="60"
10 maxPoolSize="50"
11 minPoolSize="2"
12 acquireIncrement="2"
13 user="root"
14 password="root"
15 jdbcUrl="jdbc:mysql://192.168.1.144:3306/leadtest"/>
16
17
18
20 auth="Container"
21 factory="org.apache.naming.factory.BeanFactory"
22 type="com.mchange.v2.c3p0.ComboPooledDataSource"
23 driverClass="oracle.jdbc.OracleDriver"
24 idleConnectionTestPeriod="60"
25 maxPoolSize="50"
26 minPoolSize="2"
27 acquireIncrement="2"
28 jdbcUrl="jdbc:oracle:thin:@192.168.1.229:1521:lead"
29 user="lead_oams"
30 password="p"/>
31
32
33
34
36 auth="Container"
37 factory="org.apache.naming.factory.BeanFactory"
38 type="com.mchange.v2.c3p0.ComboPooledDataSource"
39 driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
40 idleConnectionTestPeriod="60"
41 maxPoolSize="50"
42 minPoolSize="2"
43 acquireIncrement="2"
44 jdbcUrl="jdbc:sqlserver://192.168.1.51:1433;DatabaseName=demo"
45 user="sa"
46 password="p@ssw0rd"/>
47
在web.xml获得
2
3 xmlns="http://java.sun.com/xml/ns/javaee"
4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee6 http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
7
8 index.jsp
9
10
11
17
18
19 Oracle DB Connection
20 jdbc/OracleDataSource
21 javax.sql.DataSource
22 Container
23
24
25
26
27 MySQL DB Connection
28 jdbc/MysqlDataSource
29 javax.sql.DataSource
30 Container
31
32
33
34
35 SQLServer DB Connection
36 jdbc/SqlServerDataSource
37 javax.sql.DataSource
38 Container
39
40
41
附赠JdbcTemplate的一些用法
packagecn.itcast.jdbc.spring;
importjava.util.Date;
importorg.springframework.jdbc.core.BeanPropertyRowMapper;
importorg.springframework.jdbc.core.JdbcTemplate;
importcn.itcast.jdbc.JdbcUtils;
importcn.itcast.jdbc.domain.User;
publicclassJdbcTemplateCreate {
//获取数据源(设置为static 是因为该jdbc多次被调用)
staticJdbcTemplate jdbc =newJdbcTemplate(JdbcUtils.getDataSource());
publicstaticvoidmain(String[] args) {
// TODO Auto-generated method stub
//这里为测试CRUD代码块
User user = newUser();
// user.setName("小Q");
// user.setBirthday(new Date());
// user.setMoney(3000);
// text1(user);
user.setId(10);
// user.setName("BB");
// delete(user);
user = query(8);
if(user ==null) {
System.out.println("查询失败");
}else{
System.out.println("查询成功");
}
}
//插入,增加
publicstaticvoidinsert(User user) {
String sql = "insert into user(name,birthday,money)values(?,?,?)";
Object args[] = {user.getName(),user.getBirthday(),user.getMoney()};
inttemp = jdbc.update(sql, args);
if(temp >0) {
System.out.println("插入成功!");
}else{
System.out.println("插入失败");
}
}
//删除
publicstaticvoiddelete(intid) {
String sql = "delete from user where id = ?";
Object args[] = newObject[]{id};
inttemp = jdbc.update(sql,args);
if(temp >0) {
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
//更新
publicstaticvoidupdate(User user) {
String sql = "update user set name = ? where id = ?";
Object args[] = newObject[]{user.getName(),user.getId()};
inttemp = jdbc.update(sql,args);
if(temp >0) {
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
}
//查询
publicstaticUser query(intid) {
String sql = "select * from user where id = ?";
Object args[] = newObject[]{id};
Object user = jdbc.queryForObject(sql,args,newBeanPropertyRowMapper(User.class));
return(User)user;
}
}
附赠一个多数据源博客:http://blog..net/wangpeng047/article/details/8866239