JDBC 操作数据库(Access、Mysql、Mssqlserver、Oracle)

JDBC操作数据库的步骤：

一、加载数据库驱动

二、创建数据库连接

三、执行SQL语句

四、获取结果集

五、释放资源

1.JDBC 操作Access数据库

package xgn.database;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;//JDBC 连接access数据库public class access {public static void main(String[] args) throws SQLException {String connstr="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\\db1.mdb";Connection conn= DriverManager.getConnection(connstr);Statement stmt= conn.createStatement();stmt.executeUpdate("insert into user1(username,password)values('Jim','1993')");ResultSet result= stmt.executeQuery("select * from user1");while(result.next()){System.out.println(result.getString("id")+result.getString("username"));}result.close();stmt.close();conn.close();}}

2.JDBC操作Mysql

package xgn.database;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.sql.Types;//jdbc 操作mysqlpublic class mysql {static{try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockthrow new RuntimeException(e);}}/** * @param args * @throws ClassNotFoundException  */public static void main(String[] args) throws ClassNotFoundException {// TODO Auto-generated method stubString driver="com.mysql.jdbc.Driver";String connstr="jdbc:mysql://127.0.0.1:3306/stu";String user="root";String pwd="123";Connection conn=null;Statement stmt=null;ResultSet result=null;try{conn= DriverManager.getConnection(connstr, user, pwd);stmt= conn.createStatement();stmt.execute("use stu");//增stmt.executeUpdate("insert into tb1(bm,cp,sl,ssl,je)values(20,'99999',12,12,12)");//删stmt.executeUpdate("delete from tb1 where id=1");//改stmt.executeUpdate("update tb1 set cp='111111' where id=2");//查result=stmt.executeQuery("select * from tb1");while(result.next()){System.out.println(result.getString("cp"));}String sql="call adddata(?,?,?,?,?,?)";//执行存储过程CallableStatement pro_stmt= conn.prepareCall(sql);pro_stmt.setString(1, "35");pro_stmt.setString(2, "产品C");pro_stmt.setDouble(3, 12.00);pro_stmt.setDouble(4, 12.00);pro_stmt.setDouble(5, 12.00);pro_stmt.registerOutParameter(6, Types.VARCHAR);result= pro_stmt.executeQuery();//获得输出参数值System.out.println(pro_stmt.getString("cpid"));while(result.next()){System.out.println(result.getString("product"));}//事务conn.setAutoCommit(false);stmt.executeUpdate("insert into tb1(bm,cp,sl,ssl,je)values('36','产品Ｄ',12,12,12)");stmt.executeUpdate("insert into tb1(bm,cp,sl,ssl,je)values(18,'36','产品Ｄ',12,12,12)");}catch(Exception ex){System.out.println(ex.getMessage()+"aaaaaaa");System.out.println("出错已回滚!");}finally{if(result!=null){try{result.close();}catch(Exception ex){ex.printStackTrace();}}if(stmt!=null){try{stmt.close();}catch(Exception ex){ex.printStackTrace();}}if(conn!=null){try{conn.close();}catch(Exception ex){ex.printStackTrace();}}}}}

3.JDBC 操作Mssqlserver数据库

package xgn.database;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class mssqlserver {static {try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** * @param args */public static void main(String[] args) {String connstr="jdbc:sqlserver://127.0.0.1;user=sa;password=123;";Connection conn=null;Statement stmt=null;ResultSet result=null;try{conn= DriverManager.getConnection(connstr);stmt=conn.createStatement();result=stmt.executeQuery("select sname from tb1");while(result.next()){System.out.println(result.getString("sname"));}conn.close();}catch(Exception ex){ex.printStackTrace();}finally{if(result!=null){try{result.close();}catch(Exception ex){ex.printStackTrace();}}if(stmt!=null){try{stmt.close();}catch(Exception ex){ex.printStackTrace();}}if(conn!=null){try{conn.close();}catch(Exception ex){ex.printStackTrace();}}}}}

4.JDBC 操作Oracle数据库

package xgn.database;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class oracle {static{try{Class.forName("oracle.jdbc.OracleDriver");}catch(Exception ex){new RuntimeException(ex);}}public static void main(String[] args){String url="jdbc:oracle:thin:@localhost:1521:ora1";String user="scott";String pwd="19930526";Connection conn=null;Statement stmt=null;ResultSet result=null;try{conn= DriverManager.getConnection(url, user, pwd);stmt=conn.createStatement();result=stmt.executeQuery("select * from student");while(result.next()){System.out.println(result.getString("sname"));}}catch(Exception ex){ex.printStackTrace();}finally{if(result!=null){try{result.close();}catch(Exception ex){ex.printStackTrace();}}if(stmt!=null){try{result.close();}catch(Exception ex){ex.printStackTrace();}}if(conn!=null){try{conn.close();}catch(Exception ex){ex.printStackTrace();}}}}}