<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());
			}
		}