1、線程池進(jìn)行數(shù)據(jù)庫查詢-Java實現(xiàn)
調(diào)用方法
public static void main(String[] args)throws Exception {
Pool.connectDateBase();
}
創(chuàng)建線程池
public static BasicDataSource createConnectionPool () throws Exception {
BasicDataSource bds = new BasicDataSource(); //直接new連接池
bds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
bds.setUrl(url);//連哪里
bds.setUsername("scott");//用戶名
bds.setPassword("tiger");//密碼
bds.setInitialSize(3);//啟動連接池后，初始的連接數(shù)
bds.setMaxActive(100);//最大活動連接數(shù)
bds.setMaxIdle(30);//最大空閑連接數(shù)
bds.setMaxWait(10000);//最長等候時間甫恩，過時就連接失敗
bds.setMinIdle(1);//最小連接數(shù)
return bds;
}
連接服務(wù)的方法
public static void connectDateBase()throws Exception {
BasicDataSource bds = Pool.createConnectionPool();
//連接池的屬性一般都是由項目經(jīng)理在配置文件中設(shè)置好
Connection conn = bds.getConnection();
Statement st = conn.createStatement();
String sql = "select * from emp";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("empno");
String name = rs.getString(2);
double sal = rs.getDouble("sal");
System.out.println(id+","+name+","+sal);
}
rs.close();
st.close();
conn.close();
}
2、存儲過程學(xué)習(xí)-Java實現(xiàn)
數(shù)據(jù)庫創(chuàng)建
drop table users;
create table users(
u_id number(37) primary key,
uname varchar2(200) unique not null ,
upass varchar2(200) not null
);
drop sequence user_squ;
create sequence user_squ;
存儲過程創(chuàng)建
drop procedure getMax;
create or replace procedure getMax
is
begin
insert into users values (user_squ.nextval,user_squ.nextval,'123||user_squ.currval');
dbms_output.put_line('3123312');
end;
方法調(diào)用
public static void main(String[] args) throws Exception {
//Login.commitSignal();
//Login.commitSome();
//Login.commitMyself();
Login.commitProduce();
}
JDBC插入SQL語句插入數(shù)據(jù)庫
"insert into student values (null,'"+name+"','"+value+"')"單括號里面先寫兩個＂酌予，里面再寫兩個＋磺箕，里面再寫變量名
PrepareStatement進(jìn)行setString的時候，只能是數(shù)字,不能像Statement的隨意拼接了
錯誤示例: preparedStatement.setString(1, "user_squ.nextval");
刪除存儲過程
drop procedure 名
清除表的數(shù)據(jù)
truncate table 名
創(chuàng)建鏈接服務(wù)的方法
public static Connection connectDateBase(Properties properties)throws Exception {
FileInputStream inputStream = new FileInputStream("./src/db.properties");
properties.load(inputStream);
inputStream.close();
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String passwd = properties.getProperty("passwd");
Connection conn = DriverManager.getConnection(url, username, passwd);
return conn;
}
單個數(shù)據(jù)提交
public static void commitSignal() throws Exception{
Properties properties = new Properties();
Connection connection = Login.connectDateBase(properties);
String sql = "insert into users values (user_squ.nextval,?,'123||user_squ.currval')";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "小青");
preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
}
多個數(shù)據(jù)提交
public static void commitSome() throws Exception{
Properties properties = new Properties();
Connection connection = Login.connectDateBase(properties);
String sql = "insert into users values (user_squ.nextval,'123||user_squ.currval',?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
preparedStatement.setString(1, "小青");
preparedStatement.addBatch();
if (i%100==99) preparedStatement.executeBatch();
}
preparedStatement.close();
connection.close();
}
設(shè)置手動提交
public static void commitMyself() throws Exception{
Properties properties = new Properties();
Connection connection = Login.connectDateBase(properties);
connection.setAutoCommit(false);
String sql = "insert into users values (user_squ.nextval,?,'123||user_squ.currval')";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "小青");
preparedStatement.executeUpdate();
preparedStatement.close();
connection.commit();
connection.close();
}
提交事務(wù)
public static void commitProduce() throws Exception{
Properties properties = new Properties();
Connection connection = Login.connectDateBase(properties);
String sql = "call getMax()";
CallableStatement callableStatement = connection.prepareCall(sql);
for (int i = 0; i < 200; i++) {
callableStatement.execute();
}
callableStatement.close();
connection.close();
}
3抛虫、properties使用-Java實現(xiàn)
基礎(chǔ)使用
properities 屬性設(shè)置
url=jdbc:oracle:thin:@127.0.0.1:1521:XE
username=scott
passwd=tiger
簡版JDBC連接數(shù)據(jù)庫松靡，使用DML
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 先明確我們要干什么？
// 1.數(shù)據(jù)庫管理
// 2.數(shù)據(jù)庫連接
// 3.建立執(zhí)行平臺
// 4.執(zhí)行SQL語句
// 5.平臺關(guān)閉
//1.
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
//3.
Statement st = (Statement) conn.createStatement();
String sql ="insert into dept values (88,'NetWork','BOSTON')";
//4.
st.executeUpdate(sql);
//5.
st.close();
conn.close();
}
簡版JDBC連接數(shù)據(jù)庫建椰，使用Select
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 先明確我們要干什么雕欺？
// 1.數(shù)據(jù)庫管理
// 2.數(shù)據(jù)庫連接
// 3.建立執(zhí)行平臺
// 4.執(zhí)行SQL語句
// 5.平臺關(guān)閉
//1.
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
//3.
Statement st = (Statement) conn.createStatement();
String sql ="select * from dept";
st.executeQuery(sql);
//4.
ResultSet resultSet = st.getResultSet();
while (resultSet.next()) {
String num = resultSet.getString(1);
String work = resultSet.getString(2);
String loc = resultSet.getString("loc");
System.out.println(num+","+work+","+loc);
}
resultSet.close();
st.close();
conn.close();
}
簡版JDBC連接數(shù)據(jù)庫，無沖突版使用Select
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 先明確我們要干什么棉姐？
// 1.數(shù)據(jù)庫管理
// 2.數(shù)據(jù)庫連接
// 3.建立執(zhí)行平臺
// 4.執(zhí)行SQL語句
// 5.平臺關(guān)閉
//1.
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
//3.
Statement st = (Statement) conn.createStatement();
String sql ="select * from emp";
st.executeQuery(sql);
//4.
ResultSet resultSet = st.getResultSet();
ResultSetMetaData date = resultSet.getMetaData();
int count = date.getColumnCount();
while (resultSet.next()) {
for (int i = 1; i <= count; i++) {
String string = date.getColumnName(i);
String value = resultSet.getString(i);
System.out.print(string+","+value+" ");
}
System.out.println();
}
//5.
resultSet.close();
st.close();
conn.close();
}
簡版JDBC連接數(shù)據(jù)庫屠列，進(jìn)行項目分級，配置文件路徑（properties）
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 先明確我們要干什么伞矩？
// 1.找路徑
// 2.取得屬性
// 3.數(shù)據(jù)庫管理
// 4.數(shù)據(jù)庫連接
// 5.建立執(zhí)行平臺
// 6.執(zhí)行SQL語句
// 7.平臺關(guān)閉
//1.
File file =new File(".");
System.out.print(file.getAbsolutePath());
//2.
Properties properties = new Properties();
FileInputStream inputStream = new FileInputStream("./src/db.properties");
properties.load(inputStream);
inputStream.close();
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String passwd = properties.getProperty("passwd");
//3.
Connection conn = DriverManager.getConnection(url, username, passwd);
//4.
Statement st = (Statement) conn.createStatement();
//5.
String sql ="select * from emp";
st.executeQuery(sql);
//6.
ResultSet resultSet = st.getResultSet();
ResultSetMetaData date = resultSet.getMetaData();
int count = date.getColumnCount();
while (resultSet.next()) {
for (int i = 1; i <= count; i++) {
String string = date.getColumnName(i);
String value = resultSet.getString(i);
System.out.print(string+","+value+" ");
}
System.out.println();
}
//7.
resultSet.close();
st.close();
conn.close();
}
