<memo>JDBC

#JDBC：java database connectivity(connect to database)

##about JDBC
* The API is the description of classes/interfaces/methods/... that you call and use to achieve a goal. API of JDBC is in jdk，java.sql package.
* Service provider interface (SPI) is the description of classes/interfaces/methods/... that you extend and implement to achieve a goal. The SPI is an API intended to be implemented or extended by a third party. SPI of JDBC is implemented by all kinds of databases.


Put differently, the API tells you what a specific class/method does for you, and the SPI tells you what you must do to conform.

You can find the driver for the oracle database under your Oracle installation directory. (You should copy the driver to `WEB-INF/lib` of your project for a web project, or use buildpath for a java project)


##Frequently used API:
####DriverManager, Connection, Statement(preparestatement), ResultSet
* DriverManager(class): database driver, instant a Connection class:  
static Connection getConnection（String url，String user，String password）throws SQLException
    *url: ip address, oracle port
    * user: database username
    * password: database password  
  
examples of oracle and MySQL:
1.for Oracle:

```
Class.forName（"oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection（"jdbc:oracle:thin:@192.168.0.80:1521:orcl","scott","tiger");
```

2.for Mysql:
 
```
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/meing?useUnicode=true&characterEncoding=utf8","root","123");
```


* Connection(interface): 
connect to database, instant a Statement/PreparedStatement class
    `Statement createStatement（）throws SQLException`
    `PreparedStatement prepareStatement（String sql）throws SQLException`

* Statement/PreparedStatement: execute sql
    PreparedStatement can prevent from sql injection
    `int executeUpdate（）throws SQLException`
    `ResultSet excuteQuery（）throws SQLException`
    `void setX（int parameterIndex，X x）throws SQLException`

1.Statement：
`select * from users where username='scott' and password='tiger';`

```
//statement(eclipse):
String sql="select * from users where username='"+username+"' and password='"+password+"'";

public void login（String username，Sting password）{
 String str=“a”；
 String str1=“b”；
 String str2=“c”+str+“d”
}
```

2.prepareStatement：

```
String sql="select * from users where username=? and password=?";
prepareStatement.set(1,"scott");
prepareStatement.set(2,"tiger");
```

* ResultSet: search data and return result set  
    `boolean next() throws SQLException`  
    `X getX(String columnName) throws SQLException`  
    `X getX(int columnIndex) throws SQLExcpetion`






##workflow of using JDBC to CUD:
* load database driver
  there are 3 methods:
    * forName  
      `Class.forName("oracle.jdbc.driver.OracleDriver");`
    * refer database driver by setting system property  
      `System.setProperty("jdbc.driver","oracle.jdbc.driver.OracleDriver");`
    * create driver object, load database driver  
      `new oracle.jdbc.driver.OracleDriver();`

* get Connection object
  use DriverManager

```
Connection conn=null;
try{
  Class.forName(“oracle.jdbc.driver.OracleDriver”);
  conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.80:1521:orcl","scott","tiger");
}catch(ClassNotFoundException e){
}catch(SQLException e){}
```

* get Statement object, write sql

```
//Statement:
Connection conn=null;
Statement stmt=null;
try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 conn=DriverManager.getConnection("jdbc:oracle:thin:@192.106.3.80:1521:orcl","scott","tiger");
 stmt=conn.createStatement();
}catch(ClassNotFoundException e){
}catch(SQLException e){}
```
```
//PreparedStatement:
String sql="insert into user values(?,?,?,?,?)";
pstmt=conn.prepareStatement(sql);
```

* execute SQL

```
//Statement:
Connection conn=null;
Statement stmt=null;
try{
  Class.forName("oracle.jdbc.driver.OracleDriver");
  conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.80:1521:orcl","scott","tiger");
stmt=conn.createStatement();
String sql="insert into user values('wang','1','1','1928323','2015-01-01')";
stmt.excuteUpdate(sql);
}catch(ClassNotFoundException e){
}catch(SQLException e){}
```
```
//PreparedStatement:
pstmt.setString(1,username);
pstmt.setString(2,pwd);
stmt.setString(3,status);
pstmt.setString(4,qq);
stmt.setDate(5,time);
pstmt.excuteUpate();
```

* close 

```
finally{
 if(stmt!=null){stmt.close()}   【trycatch】
 if(conn!=null){conn.close()}   【trycatch】
}
```



#####example:

```
conn=cf1.getConnection();
String sql="insert into users values(?,?,?,?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, pwd);
ps.setInt(3, status);
ps.setInt(4,qq);
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date jd=null;
try {
    jd=sdf.parse(regiTime);
} catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
java.sql.Date sd=new java.sql.Date(jd.getTime());
			
ps.setDate(5, sd);
ps.executeUpdate();

```

*About Date:*

```
SimpleDateFormat sdf=new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
java.util.Date javaDate=sdf.parse(registTime);
java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime());
java.sql.Timestamp sqlTime=new java.sql.Timestamp(javaDate.getTime());
ps.setTimestamp(5, sqlTime);
```



##workflow of using JDBC to search:
1-3: 
same with JDBC to CUD

4:execute SQL

```
String sql="select * from user where username="+username;
ResultSet rs=stmt.excuteQuery(sql);
```

5:deal with the result set

```
while(rs.next()){
 System.out.println(rs.getString(1)+""+rs.getString(2)+""+rs.getString(3)+""+rs.getString(4)+""+rs.getString(5));
}
```


#####example:

```
//select single data
	public Users select(String username, String password){
		Connection conn=null;
		PreparedStatement ps=null;
		conn=connFactory.getConnection();
		Users u=null;

		String sql="select * from users where username=? and pwd=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				u=new Users();
				u.setId(rs.getInt(1));
				u.setUsername(rs.getString(2));
				u.setRegistTime(rs.getTimestamp(6));
				u.setPwd(rs.getString(3));
				u.setStatus(rs.getInt("status"));
				u.setQq(rs.getInt("qq"));
				
//				System.out.println(rs.getString("qq")+""+rs.getString("status"));
//				System.out.println(rs.getString(5)+""+rs.getString(6));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			connFactory.closeConnection(conn, ps);
		}
		return u;	
	}

Users u1=t.select("java1", "java1");
		System.out.println(u1.toString());
```


```
//select all
	public List<Users> selectAll(){
		Connection conn=null;PreparedStatement ps=null;
		conn=connFactory.getConnection();
		List<Users> userList=new ArrayList<Users>();
		Users u=null;
		String sql="select * from users";
		try {
			ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				u=new Users();
				u.setId(rs.getInt(1));
				u.setUsername(rs.getString(2));
				u.setRegistTime(rs.getTimestamp(6));
				u.setPwd(rs.getString(3));
				u.setStatus(rs.getInt("status"));
				u.setQq(rs.getInt("qq"));
				userList.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			connFactory.closeConnection(conn, ps);
		}
		return userList;
	}

List<Users> userList1=t.selectAll();
		if(userList1!=null){
			for(Users user:userList1){
				System.out.println(user.toString());
			}
		}
```