Java入门代码--数据库操作

范例12-1：连接mldn数据库
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null;// 每一个Connection对象表示一个数据库连接
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
System.out.println(conn); // 如果可以输出对象则表示连接成功
conn.close(); // 关闭连接
}
}




范例12-2：数据库创建脚本
DROP SEQUENCE myseq ;
DROP TABLE member PURGE ;
CREATE SEQUENCE myseq ;
CREATE TABLE member(
mid NUMBER  ,
name VARCHAR2(20) NOT NULL ,
age NUMBER(3) ,
birthday DATE ,
note CLOB ,
CONSTRAINT pk_mid PRIMARY KEY (mid) ,
CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 200) 
) ;




范例12-3：执行数据的增加操作
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement(); // 找到Statement接口对象
String sql = "INSERT INTO member (mid,name,age,birthday,note) VALUES "
+ " (myseq.nextval,'张三',20,SYSDATE,'MLDN Java培训')";
int len = stmt.executeUpdate(sql);// 执行SQL
System.out.println("更新行数：" + len);
stmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-4：执行数据修改操作
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement(); // 找到Statement接口对象
String sql = "UPDATE member SET name='李四',age=19,note='魔乐科技'"
+ " WHERE mid=1";
int len = stmt.executeUpdate(sql);// 执行SQL
System.out.println("更新行数：" + len);
stmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-5：执行删除操作
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement(); // 找到Statement接口对象
String sql = "DELETE FROM member WHERE mid=2";
int len = stmt.executeUpdate(sql);// 执行SQL
System.out.println("更新行数：" + len);
stmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-6：使用ResultSet取出数据
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement(); // 找到Statement接口对象
String sql = "SELECT mid,name,age,birthday,note FROM member";
ResultSet rs = stmt.executeQuery(sql) ;// 查询
while (rs.next()) {
int mid = rs.getInt("mid") ;
String name = rs.getString("name") ;
int age = rs.getInt("age") ;
Date birthday = rs.getDate("birthday") ;
String note = rs.getString("note") ;
System.out.println(mid + "，" + name + "，" + age + "，" + birthday
+ "，" + note);
}
rs.close() ; // 关闭结果集
stmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-7：通过PreparedStatement执行增加操作
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String name = "Mr'Smith" ;
Date birthday = new Date() ;// 生日为日期型
int age = 30 ;
String note = "www.mldnjava.cn" ;
String sql = "INSERT INTO member (mid,name,age,birthday,note) " +
" VALUES (myseq.nextval,?,?,?,?)";
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
pstmt.setString(1, name) ;// 设置索引数据
pstmt.setInt(2,age) ;// 设置索引数据
pstmt.setDate(3,new java.sql.Date(birthday.getTime())) ;// 设置索引数据
pstmt.setString(4, note) ;// 设置索引数据
int len = pstmt.executeUpdate() ;// 执行更新操作
System.out.println("更新行数：" + len);
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-8：查询全部操作
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String sql = "SELECT mid,name,age,birthday,note FROM member";// 没有占位符?
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
ResultSet rs = pstmt.executeQuery() ;// 查询操作
while (rs.next()) {
int mid = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
Date birthday = rs.getDate(4) ;
String note = rs.getString(5) ;
System.out.println(mid + "，" + name + "，" + age + "，" + birthday
+ "，" + note);
}
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-9：根据id查询数据
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String sql = "SELECT mid,name,age,birthday,note FROM member WHERE mid=?";
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
pstmt.setInt(1, 3) ;// 在执行之前设置内容
ResultSet rs = pstmt.executeQuery() ;// 查询操作
if (rs.next()) {// 如果有数据返回则执行，没有数据返回则不执行
int mid = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
Date birthday = rs.getDate(4) ;
String note = rs.getString(5) ;
System.out.println(mid + "，" + name + "，" + age + "，" + birthday
+ "，" + note);
} else {
System.out.println("没有查询结果！");
}
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-10：设置模糊查询
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String column = "name" ;// 模糊查询列
String keyWord = "三" ;// 查询关键字
String sql = "SELECT mid,name,age,birthday,note FROM member WHERE "
+ column + " LIKE ?";
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
pstmt.setString(1, "%" + keyWord + "%"); // 在执行之前设置内容
ResultSet rs = pstmt.executeQuery() ;// 查询操作
while (rs.next()) {// 如果有数据返回则执行，没有数据返回则不执行
int mid = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
Date birthday = rs.getDate(4) ;
String note = rs.getString(5) ;
System.out.println(mid + "，" + name + "，" + age + "，" + birthday
+ "，" + note);
} 
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-11：分页显示，Oracle使用ROWNUM完成
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String column = "name" ;// 模糊查询列
String keyWord = "" ;// 没有设置关键字，表示查询全部
int currentPage = 1 ;// 当前所在页
int lineSize = 3 ;// 每页显示的长度
String sql = " SELECT * FROM ( "
+ " SELECT mid,name,age,birthday,note,ROWNUM rn FROM member WHERE "
+ column + " LIKE ? AND ROWNUM<=?) temp " + " WHERE temp.rn>? ";
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
pstmt.setString(1, "%" + keyWord + "%"); // 在执行之前设置内容
pstmt.setInt(2, currentPage * lineSize) ;
pstmt.setInt(3, (currentPage - 1) * lineSize);
ResultSet rs = pstmt.executeQuery() ;// 查询操作
while (rs.next()) {// 如果有数据返回则执行，没有数据返回则不执行
int mid = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
Date birthday = rs.getDate(4) ;
String note = rs.getString(5) ;
System.out.println(mid + "，" + name + "，" + age + "，" + birthday
+ "，" + note);
} 
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-12：统计查询，统计数据量，统计函数使用COUNT()完成。
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
String column = "name" ;// 模糊查询列
String keyWord = "" ;// 没有设置关键字，表示查询全部
String sql = " SELECT COUNT(mid) FROM member WHERE " + column
+ " LIKE ? ";
Connection conn = null; // 每一个Connection对象表示一个数据库连接
PreparedStatement pstmt = null; // 数据库操作对象
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
pstmt = conn.prepareStatement(sql) ;// 执行SQL
pstmt.setString(1, "%" + keyWord + "%"); // 在执行之前设置内容
ResultSet rs = pstmt.executeQuery() ;// 查询操作
if (rs.next()) {// 一定会有内容返回
int count = rs.getInt(1) ;
System.out.println(count);
}
pstmt.close(); // 关闭操作，如果不关闭操作，关闭连接也是一样的
conn.close(); // 关闭连接
}
}




范例12-13：为了方便起见，使用Statement执行一次批处理
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Arrays;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null;
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement();
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三A')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三B')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三C')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三D')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三E')");
int data[] = stmt.executeBatch(); // 执行批处理
System.out.println(Arrays.toString(data));
conn.close(); // 关闭连接
}
}




范例12-14：手工出现批处理错误
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Arrays;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null;
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = conn.createStatement();
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三A')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三’B')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三C')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三D')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三E')");
int data[] = stmt.executeBatch(); // 执行批处理
System.out.println(Arrays.toString(data));
conn.close(); // 关闭连接
}
}




范例12-15：手工控制事务
package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Arrays;
public class TestDemo {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:mldn";
public static final String DBUSER = "scott";
public static final String PASSWORD = "tiger";
public static void main(String[] args) throws Exception {
Connection conn = null; // 每一个Connection对象表示一个数据库连接
Statement stmt = null ;
Class.forName(DBDRIVER); // 加载驱动程序
conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
conn.setAutoCommit(false) ;// 取消自动提交
stmt = conn.createStatement() ;
try {
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三A')") ;
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三B')") ;
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三'C')") ;
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三D')") ;
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval,'张三E')") ;
int data [] = stmt.executeBatch() ;// 执行批处理
System.out.println(Arrays.toString(data));
conn.commit() ;// 不出错提交
} catch (Exception e) {
e.printStackTrace() ;
conn.rollback() ;// 回滚事务
}
conn.close(); // 关闭连接
}
}