jdbc_demo

ORACLE|MYSQL|DB2的pom

云桌面的环境可以使用

<!--oracle-->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql/groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.32</version>
</dependency>
<!--db2-->
<dependency>
    <groupId>com.ibm.db2/groupId>
    <artifactId>db2jcc4</artifactId>
    <version>9.7.6</version>
</dependency>

jdbc的url用户名密码

mysql

driver = "com.mysql.jdbc.Driver";
url = "jdbc:mysql://127.0.0.1:3306/maqingbin?useUnicode=true&characterEncoding=utf-8";
user = "root";
password = "password";

oracle

driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@192.168.201.189:1521:xxxsidxxx";
user = "maqingbin";
password = "123456";

db2

driver = com.ibm.db2.jcc.DB2Driver
url = jdbc:db2://197.3.196.105:60000/db2pcip:currentSchema=CDC;
user = root
password = password

判断dbType类型

String databaseProductName = conn.getMetaData().getDatabaseProductName();
private DbType getDbType(String databaseProductName){
    if("MySQL".equalsIgnoreCase(databaseProductName)){
       return DbType.MYSQL;
   }else if("Oracle".equalsIgnoreCase(databaseProductName)){
       return DbType.ORACLE;
   }else if(databaseProductName.CONTAINS("DB2")){
       return DbType.DB2;;
   }else{
      throw new UnsupportedOperationException(databaseProductName+" unsupport this database");
   }
}

conn|statment|result

正常的写法

for(int i=0;i<2;i++){
	String sql="SELECT * FROM t_order WHERE user_id=?";  //后面不要写分号;
	try{
		Connection connection=dataSource.getConnetion();
		//后面的参数为了获取主键使用
		PreparedStatement preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		preparedStatement.setInt(1,10);
		preparedStatement.setTimestamp(2,new Timestamp(System.currentTimeMillis()));
		preparedStatement.setObject(3,xxxObjectxxx);//如果后台表是BLOB是序列化对象 mysql好用
		preparedStatement.setBytes(4,bytes[]);
		ResultSet resultSet = preparedStatement.executeQuery();
		while(resultSet.next()){
		 ...
		}
		//获取主键
		ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
		if (generatedKeys.next()) {
				int id = generatedKeys.getInt(1);
				String order_id = resultSet.getString("order_id");
				String user_id = resultSet.getString("user_id");
				String address_id = resultSet.getString("address_id");
				String status = resultSet.getString("status");
		}
	}
}

批量插入写

String sql="insert into order_info(order_id,cust_id,extra) values (?,?,?),(?,?,?)";  //后面不要写分号;
	Object[] params = new Object[]{"order001","cust01","aaa","order002","cust02","bbb"};
	try{
		Connection connection=dataSource.getConnetion();
		//后面的参数为了获取主键使用
		PreparedStatement preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		for(int i=0;i<params.length;i++){
			preparedStatement.setObject(i+1,params[i]);
		}
		int i=preparedStatement.executeUpdate();
		//获取主键
		ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
		if (generatedKeys.next()) {
				int id = generatedKeys.getInt(1);
				或者
				generatedKeys.getObjec(1);
		}
	}
}

mysql|oracle|db2分页