03、JDBC范例
范例：JDBC查询
|
package com.hsp;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Date;
public class testDatabase {
/** * @param args */ public static void main(String[] args) { Connection con = null; String DBname = "jdb"; //数据库名字 String url = "jdbc:mysql://localhost:3306/"+DBname+"?useSSL=true&characterEncoding=utf-8"; String username = "root";//数据库账号 String password = "root";//数据库密码 Statement sql = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, username, password);//连接代码 sql = con.createStatement(); rs = sql.executeQuery("SELECT * FROM students");//查询student表中的数据 while(rs.next()){ String number = rs.getString(1); String name = rs.getString(2); Date date = rs.getDate(3); float height = rs.getFloat(4); System.out.println("number:"+number+" name:"+name+" date:"+date+" height:"+height); } con.close();//con必须等rs调用完数据后才能关闭，否则查询不到数据 } catch (Exception e) { // TODO: handle exception System.out.println(e); } } } |
范例：封装JDBC查询
|
package com.hsp;
import java.sql.*;
public class JdbcMysql {
public static void main(String[] args) throws SQLException, ClassNotFoundException { // TODO Auto-generated method stub Connection con = null; Statement sql = null; ResultSet rs = null; con = GetDBConnection.ConnectDB("jdb", "root", "root"); String query = "SELECT * FROM STUDENTS"; if(con==null){ System.out.println("连接错误"); return ; } try { sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = sql.executeQuery(query); rs.last(); int max = rs.getRow(); System.out.println("表共有"+max+"条记录"+",随机抽取2条记录"); int a[] = GetRandomNmber.getRandomNumber(max,2); for(int i:a){ rs.absolute(i); String number = rs.getString(1); String name = rs.getString(2); Date date = rs.getDate(3); float height = rs.getFloat(4); System.out.println("number:"+number+" name:"+name+" date:"+date+" height:"+height); } con.close(); } catch (Exception e) { // TODO: handle exception System.out.println(e); } }
} |
|
package com.hsp;
import java.sql.Connection; import java.sql.DriverManager;
public class GetDBConnection { public static Connection ConnectDB(String DBname,String username,String password) { Connection con = null;
String url = "jdbc:mysql://localhost:3306/"+DBname+"?useSSL=true&characterEncoding=utf-8"; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, username, password); } catch (Exception e) { // TODO: handle exception System.out.println(e); } return con; } } |
|
package com.hsp;
import java.util.Random;
public class GetRandomNmber { public static int[] getRandomNumber(int max,int amount){ //1至max之间的amount个不同随机整数 包括(1和max) int [] randomNumber = new int[amount]; int index = 0; randomNumber[0] = -1; Random random = new Random(); while(index<amount){ int number = random.nextInt(max)+1; boolean isInArrays = false; for(int m:randomNumber){//m依次去数组randomNumber元素的值 if(m==number) isInArrays = true;//number在数组里了 } if(isInArrays==false){ //如果number不在数组randomNumber中 randomNumber[index] = number; index++; } } return randomNumber; } } |
范例：JDBC多条件查询
|
package com.hsp;
import java.sql.*;
public class JdbcMysql {
public static void main(String[] args) throws SQLException, ClassNotFoundException { // TODO Auto-generated method stub Connection con = null; Statement sql = null; ResultSet rs = null; con = GetDBConnection.ConnectDB("jdb", "root", "root"); String query = "SELECT * FROM STUDENTS"; String c1 = " year(birthday)<=2000 and month(birthday)>7";//查询条件1 String c2 = " name Like 'z_%'";//查询条件2 String c3 = " height >1.65";//查询条件3 String sqlStr = "SELECT * FROM students where "+c1+" and "+c2+" and "+c3+" order by birthday"; if(con==null){ System.out.println("连接错误"); return ; } try { sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = sql.executeQuery(sqlStr); while(rs.next()){ String number = rs.getString(1); String name = rs.getString(2); Date date = rs.getDate(3); float height = rs.getFloat(4); System.out.println("number:"+number+" name:"+name+" date:"+date+" height:"+height); } con.close(); } catch (Exception e) { // TODO: handle exception System.out.println(e); } }
} |
|
package com.hsp;
import java.sql.Connection; import java.sql.DriverManager;
public class GetDBConnection { public static Connection ConnectDB(String DBname,String username,String password) { Connection con = null;
String url = "jdbc:mysql://localhost:3306/"+DBname+"?useSSL=true&characterEncoding=utf-8"; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, username, password); } catch (Exception e) { // TODO: handle exception System.out.println(e); } return con; } } |
范例：JDBC插入数据
|
package com.hsp;
import java.sql.*;
public class JdbcMysql {
public static void main(String[] args) throws SQLException, ClassNotFoundException { // TODO Auto-generated method stub Connection con = null; Statement sql = null; ResultSet rs = null; con = GetDBConnection.ConnectDB("jdb", "root", "root"); String query = "SELECT * FROM STUDENTS"; String jilu = "('R1011','JS','2000-10-23',1.66),"+ "('R1010','LW','1989-7-22',1.76)"; String sqlStr = "insert into students values"+jilu; if(con==null){ System.out.println("连接错误"); return ; } try { sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); int ok = sql.executeUpdate(sqlStr); rs = sql.executeQuery("select * from students"); while(rs.next()){ String number = rs.getString(1); String name = rs.getString(2); Date date = rs.getDate(3); float height = rs.getFloat(4); System.out.println("number:"+number+" name:"+name+" date:"+date+" height:"+height); } con.close(); } catch (Exception e) { // TODO: handle exception System.out.println(e); } }
} |
|
package com.hsp;
import java.sql.Connection; import java.sql.DriverManager;
public class GetDBConnection { public static Connection ConnectDB(String DBname,String username,String password) { Connection con = null;
String url = "jdbc:mysql://localhost:3306/"+DBname+"?useSSL=true&characterEncoding=utf-8"; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, username, password); } catch (Exception e) { // TODO: handle exception System.out.println(e); } return con; } } |
