- //-------------------
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=****";
- Connection conn = DriverManager.getConnection(url);
- //------------------------
- Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
- Connection conn = DriverManager.getConnection("jdbc:odbc:dataBase")
- //--------------------
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url = "url=jdbc:oracle:thin:@localhost:1521:XE";
- Connection conn = DriverManager.getConnection(url,username,pwd);
- //-----------------------
- //Batch;;;数据库,专门大量数插;;
- public void method3(){
- Connection conn = null;
- PreparedStatement stmt = null;
- DBHelper db = null;
- long start = System.currentTimeMillis();
- try {
- db = new DBHelper();
- conn = db.getConn();
- //jdbc默认自动提交;;;insert commit
- // 下面这句可别创建for
- conn.setAutoCommit(false);
- String sql = "insert into t_sal values(?,'kaka',?)";
-
- stmt = conn.prepareStatement(sql);
- for (int i = 1; i < 1000; i++) {
- stmt.setInt(1, i);
- stmt.setInt(2, i*100);
- stmt.addBatch();
- if(i % 100 == 0){
- stmt.executeBatch();
- conn.commit();
- stmt.clearBatch();
-
- }
- //stmt.executeUpdate();
- }
- //conn.commit();
- //提交方式,改原来自动提交,,一句一提交..
- conn.setAutoCommit(true);
- } catch (SQLException e) {
- try {
- conn.rollback();
- } catch (SQLException e1) {
- e1.printStackTrace();
- }
- e.printStackTrace();
- } finally {
- db.close(null, stmt, conn);
- }
- long end = System.currentTimeMillis();
- //ps bach一共花了578毫秒
- System.out.println("ps bach一共花了"+(end - start)+"毫秒");
-
- }
- //-----取出刚insert记录的自增id-----第一种方法...
- public int getInsert_id1(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt_insert = null;
- PreparedStatement pstmt_select = null;
- ResultSet rs = null;
- int row = 0;
-
- String insertSQL = "insert into pro_stu values(pro_stu_id.NEXTVAL,'tom','13912345678','重庆西永')";
- //第二种方法...selectSQL = "select pro_stu_id.CURRVAL from dual";
- String selectSQL = "select max(stu_id) from pro_stu";
- try{
- //执行插入语句...
- pstmt_insert = conn.prepareStatement(insertSQL);
- pstmt_insert.executeUpdate();
- //执行查询语句...
- pstmt_select = conn.prepareStatement(selectSQL);
- rs = pstmt_select.executeQuery();
- while(rs.next()){
- row = rs.getInt(1);
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- db.close(rs, pstmt_select, null);
- db.close(null, pstmt_insert, conn);
-
- return row;
- }
- //----------取出刚insert记录通用方法.............
- public int getInsert_id2(){
- DBHelper db = new DBHelper();
-
- Connection conn = db.getConn();
-
- PreparedStatement pstmt_insert = null;
- //不曾见,,数据库封装对象;;知道..数据库版;
- DatabaseMetaData dbmd = null;
- ResultSet rs = null;
- int row = 0;
-
- /*首先你要知道,你的jdbc是不是 3.0是否支持功能
- System.out.println("product name"+dmd.getDatabaseProductName());
- System.out.println("product version"+dmd.getDatabaseProductVersion());
- System.out.println("support generate keys?"+dmd.supportsGetGeneratedKeys());
- */
-
- String sql = "insert into pro_stu values" +
- "(pro_stu_id.NEXTVAL,'jack','13812345678','广东深圳')";
- try{
- dbmd = conn.getMetaData();
-
- pstmt_insert = conn.prepareStatement(sql,new String[]{"stu_id"});
- pstmt_insert.executeUpdate();
-
- if(dbmd.supportsGetGeneratedKeys()){
- rs = pstmt_insert.getGeneratedKeys();
- if(rs.next()){
- row = rs.getInt(1);
- }
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- db.close(rs, pstmt_insert, conn);
- return row;
- }
- //事务-----------------
- public void inTimeRun(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt1 = null;
- PreparedStatement pstmt2 = null;
- try{
- //取消自动提交更新设置为手动...
- conn.setAutoCommit(false);
-
- String sql1 = "update pro_stu set stu_name = 'jarry' where stu_id = 6";
- String sql2 = "delete from pro_stu where stu_id = 10";
-
- pstmt1 = conn.prepareStatement(sql1);
- pstmt2 = conn.prepareStatement(sql2);
-
- pstmt1.executeUpdate();
- pstmt2.executeUpdate();
-
- //System.out.println("正在更新...");
-
- conn.commit();
-
-
- }catch(SQLException e){
- e.printStackTrace();
- try{
- System.out.println("更新失败...已回滚....");
- //设置回自动提交更新...
- conn.rollback();
- conn.setAutoCommit(true);
- }catch(SQLException e1){
- e1.printStackTrace();
- }
- }
- db.close(null, pstmt2, null);
- db.close(null, pstmt1, conn);
- }
- public class SaveFile {
- public void saveF(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt = null;
- try{
- pstmt = conn.prepareStatement("insert into saveImg values(?,?)");
-
- pstmt.setString(1,"e:/西湖醋鱼.jpg");
- InputStream in = new FileInputStream("e:/西湖醋鱼.jpg");
- pstmt.setBinaryStream(2,in,in.available());
- pstmt.executeQuery();
- System.out.println("图片保存成功...");
- }catch(SQLException e){
- e.printStackTrace();
- }catch(FileNotFoundException e1){
- e1.printStackTrace();
- }catch(IOException e2){
- e2.printStackTrace();
- }
- }
- public void getF(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try{
- pstmt = conn.prepareStatement("select * from saveImg where filename = ?");
- pstmt.setString(1,"e:/西湖醋鱼.jpg");
- rs = pstmt.executeQuery();
- rs.next();
- InputStream in = rs.getBinaryStream("filecontent");
- FileOutputStream fos = new FileOutputStream("e:/index.jpg");
- byte[] buff = new byte[1024];
- int length = 0;
- while((length = in.read(buff)) != -1){
- fos.write(buff,0,length);
- fos.flush();
- }
- fos.close();
- in.close();
- System.out.println("数据写入成功...");
- }catch(SQLException e){
- e.printStackTrace();
- }catch(FileNotFoundException e1){
- e1.printStackTrace();
- }catch(IOException e2){
- e2.printStackTrace();
- }
- }
- public static void main(String[] args){
- SaveFile save = new SaveFile();
- save.getF();
- }
- }
- 1:在conf/context.xml中加入<resource>元素
- <Resource name="OracleDataSource"
- auth="Container"
- type="javax.sql.DataSource"
- maxActive= "10"
- maxIdle= "2"
- maxWait="5000"
- username="scott"
- password="tiger"
- driverClassName="oracle.jdbc.driver.OracleDriver"
- url="jdbc:oracle:thin:@localhost:1521:myOracle"/>
-
- 2:
- 在web.xml中加入<resource-ref>元素
- <resource-ref>元素表示在Web应用中引用JNDI资源
- <resource-ref>
- <description>DB Connection</description>
- <res-ref-name>OracleDataSource</res-ref-name>
- <res-type>javax.sql.DataSource</res-type>
- <res-auth>Container</res-auth>
- </resource-ref>
-
- 3:
- 由于数据源由Servlet容器创建并维护,所以必须把JDBC驱动程序拷贝到Tomcat安装目录下的lib目录下,确保Servlet容器能够访问驱动程序
-
- 4:
- 获得对数据源的引用:
- Context ctx = new InitialContext();
- DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/OracleDataSource");
- 获得数据库连接对象:
- Connection con = ds.getConnection();
- 返回数据库连接到连接池:
- con.close();
- //-------------------
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=****";
- Connection conn = DriverManager.getConnection(url);
- //------------------------
- Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
- Connection conn = DriverManager.getConnection("jdbc:odbc:dataBase")
- //--------------------
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url = "url=jdbc:oracle:thin:@localhost:1521:XE";
- Connection conn = DriverManager.getConnection(url,username,pwd);
- //-----------------------
- //Batch;;;数据库,专门大量数插;;
- public void method3(){
- Connection conn = null;
- PreparedStatement stmt = null;
- DBHelper db = null;
- long start = System.currentTimeMillis();
- try {
- db = new DBHelper();
- conn = db.getConn();
- //jdbc默认自动提交;;;insert commit
- // 下面这句可别创建for
- conn.setAutoCommit(false);
- String sql = "insert into t_sal values(?,'kaka',?)";
- stmt = conn.prepareStatement(sql);
- for (int i = 1; i < 1000; i++) {
- stmt.setInt(1, i);
- stmt.setInt(2, i*100);
- stmt.addBatch();
- if(i % 100 == 0){
- stmt.executeBatch();
- conn.commit();
- stmt.clearBatch();
- }
- //stmt.executeUpdate();
- }
- //conn.commit();
- //提交方式,改原来自动提交,,一句一提交..
- conn.setAutoCommit(true);
- } catch (SQLException e) {
- try {
- conn.rollback();
- } catch (SQLException e1) {
- e1.printStackTrace();
- }
- e.printStackTrace();
- } finally {
- db.close(null, stmt, conn);
- }
- long end = System.currentTimeMillis();
- //ps bach一共花了578毫秒
- System.out.println("ps bach一共花了"+(end - start)+"毫秒");
- }
- //-----取出刚insert记录的自增id-----第一种方法...
- public int getInsert_id1(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt_insert = null;
- PreparedStatement pstmt_select = null;
- ResultSet rs = null;
- int row = 0;
- String insertSQL = "insert into pro_stu values(pro_stu_id.NEXTVAL,'tom','13912345678','重庆西永')";
- //第二种方法...selectSQL = "select pro_stu_id.CURRVAL from dual";
- String selectSQL = "select max(stu_id) from pro_stu";
- try{
- //执行插入语句...
- pstmt_insert = conn.prepareStatement(insertSQL);
- pstmt_insert.executeUpdate();
- //执行查询语句...
- pstmt_select = conn.prepareStatement(selectSQL);
- rs = pstmt_select.executeQuery();
- while(rs.next()){
- row = rs.getInt(1);
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- db.close(rs, pstmt_select, null);
- db.close(null, pstmt_insert, conn);
- return row;
- }
- //----------取出刚insert记录通用方法.............
- public int getInsert_id2(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt_insert = null;
- //不曾见,,数据库封装对象;;知道..数据库版;
- DatabaseMetaData dbmd = null;
- ResultSet rs = null;
- int row = 0;
- /*首先你要知道,你的jdbc是不是 3.0是否支持功能
- System.out.println("product name"+dmd.getDatabaseProductName());
- System.out.println("product version"+dmd.getDatabaseProductVersion());
- System.out.println("support generate keys?"+dmd.supportsGetGeneratedKeys());
- */
- String sql = "insert into pro_stu values" +
- "(pro_stu_id.NEXTVAL,'jack','13812345678','广东深圳')";
- try{
- dbmd = conn.getMetaData();
- pstmt_insert = conn.prepareStatement(sql,new String[]{"stu_id"});
- pstmt_insert.executeUpdate();
- if(dbmd.supportsGetGeneratedKeys()){
- rs = pstmt_insert.getGeneratedKeys();
- if(rs.next()){
- row = rs.getInt(1);
- }
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- db.close(rs, pstmt_insert, conn);
- return row;
- }
- //事务-----------------
- public void inTimeRun(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt1 = null;
- PreparedStatement pstmt2 = null;
- try{
- //取消自动提交更新设置为手动...
- conn.setAutoCommit(false);
- String sql1 = "update pro_stu set stu_name = 'jarry' where stu_id = 6";
- String sql2 = "delete from pro_stu where stu_id = 10";
- pstmt1 = conn.prepareStatement(sql1);
- pstmt2 = conn.prepareStatement(sql2);
- pstmt1.executeUpdate();
- pstmt2.executeUpdate();
- //System.out.println("正在更新...");
- conn.commit();
- }catch(SQLException e){
- e.printStackTrace();
- try{
- System.out.println("更新失败...已回滚....");
- //设置回自动提交更新...
- conn.rollback();
- conn.setAutoCommit(true);
- }catch(SQLException e1){
- e1.printStackTrace();
- }
- }
- db.close(null, pstmt2, null);
- db.close(null, pstmt1, conn);
- }
- public class SaveFile {
- public void saveF(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt = null;
- try{
- pstmt = conn.prepareStatement("insert into saveImg values(?,?)");
- pstmt.setString(1,"e:/西湖醋鱼.jpg");
- InputStream in = new FileInputStream("e:/西湖醋鱼.jpg");
- pstmt.setBinaryStream(2,in,in.available());
- pstmt.executeQuery();
- System.out.println("图片保存成功...");
- }catch(SQLException e){
- e.printStackTrace();
- }catch(FileNotFoundException e1){
- e1.printStackTrace();
- }catch(IOException e2){
- e2.printStackTrace();
- }
- }
- public void getF(){
- DBHelper db = new DBHelper();
- Connection conn = db.getConn();
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try{
- pstmt = conn.prepareStatement("select * from saveImg where filename = ?");
- pstmt.setString(1,"e:/西湖醋鱼.jpg");
- rs = pstmt.executeQuery();
- rs.next();
- InputStream in = rs.getBinaryStream("filecontent");
- FileOutputStream fos = new FileOutputStream("e:/index.jpg");
- byte[] buff = new byte[1024];
- int length = 0;
- while((length = in.read(buff)) != -1){
- fos.write(buff,0,length);
- fos.flush();
- }
- fos.close();
- in.close();
- System.out.println("数据写入成功...");
- }catch(SQLException e){
- e.printStackTrace();
- }catch(FileNotFoundException e1){
- e1.printStackTrace();
- }catch(IOException e2){
- e2.printStackTrace();
- }
- }
- public static void main(String[] args){
- SaveFile save = new SaveFile();
- save.getF();
- }
- }
- 1:在conf/context.xml中加入<resource>元素
- <Resource name="OracleDataSource"
- auth="Container"
- type="javax.sql.DataSource"
- maxActive= "10"
- maxIdle= "2"
- maxWait="5000"
- username="scott"
- password="tiger"
- driverClassName="oracle.jdbc.driver.OracleDriver"
- url="jdbc:oracle:thin:@localhost:1521:myOracle"/>
- 2:
- 在web.xml中加入<resource-ref>元素
- <resource-ref>元素表示在Web应用中引用JNDI资源
- <resource-ref>
- <description>DB Connection</description>
- <res-ref-name>OracleDataSource</res-ref-name>
- <res-type>javax.sql.DataSource</res-type>
- <res-auth>Container</res-auth>
- </resource-ref>
- 3:
- 由于数据源由Servlet容器创建并维护,所以必须把JDBC驱动程序拷贝到Tomcat安装目录下的lib目录下,确保Servlet容器能够访问驱动程序
- 4:
- 获得对数据源的引用:
- Context ctx = new InitialContext();
- DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/OracleDataSource");
- 获得数据库连接对象:
- Connection con = ds.getConnection();
- 返回数据库连接到连接池:
- con.close();