Quick Setup a Project Using JDBC & Derby DB/Java DB

Categories: DatabaseJava; Tagged with: ; @ May 8th, 2014 0:03

Set up Maven dependences

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyclient</artifactId>
<version>10.10.2.0</version>
</dependency>

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.10.2.0</version>
</dependency>

Connect to the DB using JDBC

This method will print out all Tables;  // before you do anything with the Database, there should be some System tables already.

package com.liguoliang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCTest {

	private static final String DB_URL = "jdbc:derby:tempDBForTest;create=true";	
	public static void main(String[] args) {
	    Connection conn = null ;
	    try {
	        Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	        conn = DriverManager.getConnection(DB_URL);
	        
	        String sql = "SELECT TABLENAME FROM SYS.SYSTABLES";
			PreparedStatement ps = conn.prepareStatement(sql);
	        
	        ResultSet rs = ps.executeQuery();
	        while (rs.next()) {
				System.out.println("Rs: " + rs.getString(1));
			}
	    } catch (SQLException se) {
	         se.printStackTrace();
	    } catch(ClassNotFoundException e){
	        System.out.println("JDBC Driver not found in CLASSPATH") ;
	    }finally {
	        if(conn != null){
	            try{
	                conn.close() ;
	            } catch(SQLException se){
	                se.printStackTrace();
	            }
	        }
	    }
	}

}

Use Eclipse to manage your Derby

Switch to ‘Database Development’ perspective, you may create/manage  Derby DB/table.

JDBC Statement SQL Injection

Categories: Java; Tagged with: ; @ May 7th, 2014 23:54

Here is an example to inject dirty SQL using JDBC Statement:

package com.liguoliang.j2ee;

import static org.junit.Assert.*;

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.Date;

import org.junit.Before;
import org.junit.Test;

public class TestJDBC {

	private static final String DB_URL = "jdbc:derby:C:\\Users\\Guoliang\\MyDB;create=true";
	
	Connection conn = null ;
	
	@Before
	public void before() throws ClassNotFoundException, SQLException {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	    conn = DriverManager.getConnection(DB_URL);
	}
	
	@Test
	public void testSQLInjection() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password" + new Date().toString();
		
		Statement statement = conn.createStatement();
		String sql = "SELECT * FROM USERS WHERE USER_NAME = '" + userName + "' AND PASSWORD = '" + password + "'";
		System.out.println(sql);
		ResultSet rs = statement.executeQuery(sql);
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			System.out.println(" > User Id: " + userId); // Will print all user id;
		}
		assertTrue(userId != -1);
	}
	
	@Test
	public void testPreparedStatement() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password";
		
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERS WHERE USER_NAME = ? AND PASSWORD = ?");
		ps.setString(1, userName);
		ps.setString(2, password);
		
		ResultSet rs = ps.executeQuery();
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			fail("User Id: " + userId);
		}
		
		System.out.println("userId: " + userId);
	}

}

I set one of the paramters to : [guoliang ‘a’ = ‘a], the I got the SQL:

SELECT * FROM USERS WHERE USER_NAME = 'guoliang' OR 'a' = 'a' AND PASSWORD = 'wrong-passwordWed May 07 23:47:11 CST 2014'

This means, the SQL will return all records in this table.

However we can use prepared statement to solve this issue.

SQLs used by this example:

CREATE TABLE USERS (ID INT, USER_NAME VARCHAR(20), PASSWORD VARCHAR(20));
INSERT INTO USERS (ID, USER_NAME, PASSWORD) VALUES (1, 'guoliang', 'password_abc');

 

Oracle 11 XE & JDBC

Categories: Java; Tagged with: ; @ May 1st, 2013 0:51

How to get Oracle 11 XE for Windows 64?

Download the Oracle 11 XE 32 bit and install it.

No problem for me ( Win7 64)

Forgot the sys password, how to reset?

Conn / as sysdba

passw system

input new password.

[http://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/]

How to get the version of Oracle?

select * from v$version

How to Add Oracle JDBC Driver in Loacl Maven Repo?

mvn install:install-file -Dfile=C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0 -Dpackaging=jar
then add modify your pom:

<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0</version>
</dependency>
[http://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/]

Quick test using JDBC

	public static void main(String[] args) throws Throwable {
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection connection = null;
		connection = DriverManager.getConnection(
			"jdbc:oracle:thin:@localhost:1521:xe","root","root");
		System.out.println(connection.getMetaData().getDatabaseMajorVersion());
		connection.close();
	}

Using Spring JDBC Template

Categories: Java; Tagged with: ; @ June 25th, 2012 21:48

JDBCTemplate simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results.
http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/core/JdbcTemplate.html

Here are two methods, the 2nd one is using JDBCTemplate from Srping, you can see the code are more simple and clear:

	/**
	 * LoadUsers using JDBC.
	 * @param dataSource
	 * @return
	 */
	public List loadUserJDBC(DataSource dataSource) {
		List users = new ArrayList();
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement preparedStatement = conn.prepareStatement("SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER");
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				User user = new User();
				users.add(user);
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				System.out.println(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(conn != null) {
				
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return users;
	}
	
	/**
	 * Load users using Spring JDBCTemplate.
	 * @param jdbcTemplate
	 * @return
	 */
	public java.util.List doJDBCTempQuery(JdbcTemplate jdbcTemplate) {
		String queryUsers = "SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER";
		java.util.List users = jdbcTemplate.query(queryUsers, new ParameterizedRowMapper() {

			@Override
			public User mapRow(ResultSet rs, int arg1) throws SQLException {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(4));
				return user;
			}
		});
		
		return users;
	}

And here is a example to define the template bean:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost/mini-liguoliang.com" />
    <property name="username" value="root" />
    <property name="password" value="pw" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<constructor-arg ref="dataSource"></constructor-arg>
</bean>

SQL中判断时间

Categories: Java; Tagged with: ; @ November 19th, 2010 20:58

需求: (使用JDBC) 在SQL中增加时间限制

简单错误的写法:

SimpleDateFormat df = new SimpleDateFormat(“yyyy-MM-dd”);
String dateNow = df.format(new Date(System.currentTimeMillis())); //”2010-11-19″; //new Date(System.currentTimeMillis()).toString();
然后将dateNow加入到SQL中. maybe可工作, 但不能保证数据库平台迁移后仍可继续工作.

正确的解决方法, 使用PreparedStatement设定SQL参数:

StringBuilder sb = new StringBuilder("SELECT oue FROM OUEnroll oue WHERE oue.user_ID = ");
		sb.append(user.getUser_ID()).append("  AND (oue.dateStart IS NULL OR ").append("?2")...

// 省略若干
		select.setParameter(1, user.getUser_ID());
		select.setParameter(2, new Date(System.currentTimeMillis()));

JDBC会根据具体的数据库Driver处理Date并加入到SQL中.

 

Older Posts



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.