目的:想通过jdbc连接在数据库里面搞出两张有1对1关联关系的表。
一个班,一台电脑对应一个学生,一个学生对应一台电脑,并且如果有学生走了,来了新学生,可以再对应上走的那位同学的电脑........(if语句)
computer类package com.kettas.jdbc.o2o;public class Computer {
private Integer cid;
private String serialnum;
private String type;
private Student student;
public Computer(String serialnum, String type) {
super();
this.serialnum = serialnum;
this.type = type;
.........构造方法,set,get方法.......................................
}student类package com.kettas.jdbc.o2o;public class Student {
private Integer sid;
private String sname;
private int age;
private Computer computer; public Student(String sname, int age) {
super();
this.sname = sname;
this.age = age;
构造方法,set,get方法.......................................
}
}studentDao类package com.kettas.jdbc.o2o;
import java.sql.*;
import com.kettas.jdbc.util.*;public class StudentDao {
public void insert(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
//1. insert stu-----> name, age
Integer sid=IdGenerator.getOid("jdbc_student_seq");
String sql="insert into jdbc_student(sid,sname,age) "
+" values(?,?,?)";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, sid);
pstm.setString(2, stu.getSname());
pstm.setInt(3, stu.getAge());
pstm.executeUpdate();
// 2. oid=pk ----->sid
stu.setSid(sid);
// 3. relationship field
Computer com=stu.getComputer();
if(com==null) return;
if(com.getCid()==null){
// insert computer
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
sql="insert into jdbc_computer(cid,serialnum,type,student_id)"
+" values(?,?,?,?)";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, cid);
pstmComputer.setString(2,com.getSerialnum());
pstmComputer.setString(3, com.getType());
pstmComputer.setInt(4, sid);
pstmComputer.executeUpdate();
com.setCid(cid);
}
if(com.getCid()!=null){
// update computer fk
sql="update jdbc_computer set student_id=? where cid=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,sid);
pstmComputer.setInt(2,com.getCid());
pstmComputer.executeUpdate();
}
}finally{
JdbcUtil.release(null, pstm, null);
JdbcUtil.release(null, pstmComputer, null);
}
}
public void update(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
// 1. update stu----> name age
String sql="update jdbc_student set sname=?,age=? where sid=?";
pstm=conn.prepareStatement(sql);
pstm.setString(1, stu.getSname());
pstm.setInt(2, stu.getAge());
pstm.setInt(3, stu.getSid());
pstm.executeUpdate();
// 2. update relation field----> computer
Computer com=stu.getComputer();
if(com!=null && com.getCid()!=null ){//换电脑
// update computer;
sql="update jdbc_computer set student_id=? where cid=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,stu.getSid());
pstmComputer.setInt(2,com.getCid());
pstmComputer.executeUpdate();
}
if(com==null){//没电脑
// update computer fk=null
sql="update jdbc_computer set student_id=null where student_id=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,stu.getSid());
pstmComputer.executeUpdate();
}
if(com!=null && com.getCid()==null ){
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
sql="insert into jdbc_computer(cid,serialnum,type,student_id)"
+" values(?,?,?,?)";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, cid);
pstmComputer.setString(2,com.getSerialnum());
pstmComputer.setString(3, com.getType());
pstmComputer.setInt(4, stu.getSid());
pstmComputer.executeUpdate();
com.setCid(cid);
}
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void delete(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
// 1. relationship field ----> update
String sql="update jdbc_computer set student_id=null where student_id=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, stu.getSid());
pstmComputer.executeUpdate();
// 2. delete stu
sql="delete from jdbc_student where sid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, stu.getSid());
pstm.executeUpdate();
// 3. stu.sid==null
stu.setSid(null);
}finally{
JdbcUtil.release(null,pstmComputer, null);
JdbcUtil.release(null,pstm, null);
}
}
/*public Student queryBySid1(Integer sid) throws Exception{
return null;
}*/
public Student queryBySid2(Integer sid) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try{
conn=JdbcUtil.getConnection();
// outer join
String sql="select s.sid,s.sname,s.age,c.cid,c.serialnum,c.type "
+" from jdbc_student s left outer join jdbc_computer c"
+" on s.sid=c.student_id"
+" where s.sid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, sid);
rs=pstm.executeQuery();
Student stu=null;
if(rs.next()){
stu=new Student();
stu.setSid(rs.getInt(1));
stu.setSname(rs.getString(2));
stu.setAge(rs.getInt(3));
if(rs.getString(5)!=null){
Computer c=new Computer();
c.setCid(rs.getInt(4));
c.setSerialnum(rs.getString(5));
c.setType(rs.getString(6));
stu.setComputer(c);
c.setStudent(stu);
}
}
return stu;
}finally{
JdbcUtil.release(rs, pstm, null);
}
}
}
computerDao类package com.kettas.jdbc.o2o;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;import com.kettas.jdbc.util.IdGenerator;
import com.kettas.jdbc.util.JdbcUtil;public class ComputerDao {
public void insert(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
String sql="insert into jdbc_computer(cid,serialnum,type) "
+" values(?,?,?)";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, cid);
pstm.setString(2, com.getSerialnum());
pstm.setString(3, com.getType());
pstm.executeUpdate();
// 2. oid=pk ----->sid
com.setCid(cid);
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void update(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
String sql="update jdbc_computer set serialnum=?,type=? where cid=?";
pstm=conn.prepareStatement(sql);
pstm.setString(1, com.getSerialnum());
pstm.setString(2, com.getType());
pstm.setInt(3, com.getCid());
pstm.executeUpdate();
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void delete(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
String sql="delete from jdbc_computer where cid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, com.getCid());
pstm.executeUpdate();
}finally{
JdbcUtil.release(null,pstm, null);
}
}
public Computer queryByCid(Integer cid) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try{
conn=JdbcUtil.getConnection();
String sql="select s.sid,s.sname,s.age,c.cid,c.serialnum,c.type "
+" from jdbc_student s left outer join jdbc_computer c"
+" on s.sid=c.student_id"
+" where c.cid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, cid);
rs=pstm.executeQuery();
Computer com=null;
if(rs.next()){
com=new Computer();
com.setCid(rs.getInt(4));
com.setSerialnum(rs.getString(5));
com.setType(rs.getString(6));
if(rs.getString(2)!=null){
Student s=new Student();
s.setSid(rs.getInt(1));
s.setSname(rs.getString(2));
s.setAge(rs.getInt(3));
com.setStudent(s);
s.setComputer(com);
}
}
return com;
}finally{
JdbcUtil.release(rs, pstm, null);
}
}
}
test类package com.kettas.jdbc.o2o;
import com.kettas.jdbc.util.*;
public class Testo2o { public static void main(String[] args) throws Exception {
/* 查询
* StudentDao dao=new StudentDao();
* Student stu=dao.queryBySid2(1);
System.out.println(stu.getSname()+"------"+stu.getComputer().getSerialnum());
*/
/*添加
* StudentDao dao=new StudentDao();
* Student s1=new Student("lwc",21);
Computer c1=new Computer("222","ibm");
s1.setComputer(c1);
c1.setStudent(s1);
dao.insert(s1);
*/
/*删除
* StudentDao dao=new StudentDao();
* Student stu=dao.queryBySid2(2);
dao.delete(stu);*/
JdbcUtil.getConnection().close();
}}自学学得头晕,已经不知道南北了,往大家指点。讲思路也行。我这程序写对了吗?
一个班,一台电脑对应一个学生,一个学生对应一台电脑,并且如果有学生走了,来了新学生,可以再对应上走的那位同学的电脑........(if语句)
computer类package com.kettas.jdbc.o2o;public class Computer {
private Integer cid;
private String serialnum;
private String type;
private Student student;
public Computer(String serialnum, String type) {
super();
this.serialnum = serialnum;
this.type = type;
.........构造方法,set,get方法.......................................
}student类package com.kettas.jdbc.o2o;public class Student {
private Integer sid;
private String sname;
private int age;
private Computer computer; public Student(String sname, int age) {
super();
this.sname = sname;
this.age = age;
构造方法,set,get方法.......................................
}
}studentDao类package com.kettas.jdbc.o2o;
import java.sql.*;
import com.kettas.jdbc.util.*;public class StudentDao {
public void insert(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
//1. insert stu-----> name, age
Integer sid=IdGenerator.getOid("jdbc_student_seq");
String sql="insert into jdbc_student(sid,sname,age) "
+" values(?,?,?)";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, sid);
pstm.setString(2, stu.getSname());
pstm.setInt(3, stu.getAge());
pstm.executeUpdate();
// 2. oid=pk ----->sid
stu.setSid(sid);
// 3. relationship field
Computer com=stu.getComputer();
if(com==null) return;
if(com.getCid()==null){
// insert computer
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
sql="insert into jdbc_computer(cid,serialnum,type,student_id)"
+" values(?,?,?,?)";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, cid);
pstmComputer.setString(2,com.getSerialnum());
pstmComputer.setString(3, com.getType());
pstmComputer.setInt(4, sid);
pstmComputer.executeUpdate();
com.setCid(cid);
}
if(com.getCid()!=null){
// update computer fk
sql="update jdbc_computer set student_id=? where cid=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,sid);
pstmComputer.setInt(2,com.getCid());
pstmComputer.executeUpdate();
}
}finally{
JdbcUtil.release(null, pstm, null);
JdbcUtil.release(null, pstmComputer, null);
}
}
public void update(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
// 1. update stu----> name age
String sql="update jdbc_student set sname=?,age=? where sid=?";
pstm=conn.prepareStatement(sql);
pstm.setString(1, stu.getSname());
pstm.setInt(2, stu.getAge());
pstm.setInt(3, stu.getSid());
pstm.executeUpdate();
// 2. update relation field----> computer
Computer com=stu.getComputer();
if(com!=null && com.getCid()!=null ){//换电脑
// update computer;
sql="update jdbc_computer set student_id=? where cid=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,stu.getSid());
pstmComputer.setInt(2,com.getCid());
pstmComputer.executeUpdate();
}
if(com==null){//没电脑
// update computer fk=null
sql="update jdbc_computer set student_id=null where student_id=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1,stu.getSid());
pstmComputer.executeUpdate();
}
if(com!=null && com.getCid()==null ){
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
sql="insert into jdbc_computer(cid,serialnum,type,student_id)"
+" values(?,?,?,?)";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, cid);
pstmComputer.setString(2,com.getSerialnum());
pstmComputer.setString(3, com.getType());
pstmComputer.setInt(4, stu.getSid());
pstmComputer.executeUpdate();
com.setCid(cid);
}
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void delete(Student stu) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
PreparedStatement pstmComputer=null;
try{
conn=JdbcUtil.getConnection();
// 1. relationship field ----> update
String sql="update jdbc_computer set student_id=null where student_id=?";
pstmComputer=conn.prepareStatement(sql);
pstmComputer.setInt(1, stu.getSid());
pstmComputer.executeUpdate();
// 2. delete stu
sql="delete from jdbc_student where sid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, stu.getSid());
pstm.executeUpdate();
// 3. stu.sid==null
stu.setSid(null);
}finally{
JdbcUtil.release(null,pstmComputer, null);
JdbcUtil.release(null,pstm, null);
}
}
/*public Student queryBySid1(Integer sid) throws Exception{
return null;
}*/
public Student queryBySid2(Integer sid) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try{
conn=JdbcUtil.getConnection();
// outer join
String sql="select s.sid,s.sname,s.age,c.cid,c.serialnum,c.type "
+" from jdbc_student s left outer join jdbc_computer c"
+" on s.sid=c.student_id"
+" where s.sid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, sid);
rs=pstm.executeQuery();
Student stu=null;
if(rs.next()){
stu=new Student();
stu.setSid(rs.getInt(1));
stu.setSname(rs.getString(2));
stu.setAge(rs.getInt(3));
if(rs.getString(5)!=null){
Computer c=new Computer();
c.setCid(rs.getInt(4));
c.setSerialnum(rs.getString(5));
c.setType(rs.getString(6));
stu.setComputer(c);
c.setStudent(stu);
}
}
return stu;
}finally{
JdbcUtil.release(rs, pstm, null);
}
}
}
computerDao类package com.kettas.jdbc.o2o;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;import com.kettas.jdbc.util.IdGenerator;
import com.kettas.jdbc.util.JdbcUtil;public class ComputerDao {
public void insert(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
Integer cid=IdGenerator.getOid("jdbc_computer_seq");
String sql="insert into jdbc_computer(cid,serialnum,type) "
+" values(?,?,?)";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, cid);
pstm.setString(2, com.getSerialnum());
pstm.setString(3, com.getType());
pstm.executeUpdate();
// 2. oid=pk ----->sid
com.setCid(cid);
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void update(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
String sql="update jdbc_computer set serialnum=?,type=? where cid=?";
pstm=conn.prepareStatement(sql);
pstm.setString(1, com.getSerialnum());
pstm.setString(2, com.getType());
pstm.setInt(3, com.getCid());
pstm.executeUpdate();
}finally{
JdbcUtil.release(null, pstm, null);
}
}
public void delete(Computer com) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
try{
conn=JdbcUtil.getConnection();
String sql="delete from jdbc_computer where cid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, com.getCid());
pstm.executeUpdate();
}finally{
JdbcUtil.release(null,pstm, null);
}
}
public Computer queryByCid(Integer cid) throws Exception{
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try{
conn=JdbcUtil.getConnection();
String sql="select s.sid,s.sname,s.age,c.cid,c.serialnum,c.type "
+" from jdbc_student s left outer join jdbc_computer c"
+" on s.sid=c.student_id"
+" where c.cid=?";
pstm=conn.prepareStatement(sql);
pstm.setInt(1, cid);
rs=pstm.executeQuery();
Computer com=null;
if(rs.next()){
com=new Computer();
com.setCid(rs.getInt(4));
com.setSerialnum(rs.getString(5));
com.setType(rs.getString(6));
if(rs.getString(2)!=null){
Student s=new Student();
s.setSid(rs.getInt(1));
s.setSname(rs.getString(2));
s.setAge(rs.getInt(3));
com.setStudent(s);
s.setComputer(com);
}
}
return com;
}finally{
JdbcUtil.release(rs, pstm, null);
}
}
}
test类package com.kettas.jdbc.o2o;
import com.kettas.jdbc.util.*;
public class Testo2o { public static void main(String[] args) throws Exception {
/* 查询
* StudentDao dao=new StudentDao();
* Student stu=dao.queryBySid2(1);
System.out.println(stu.getSname()+"------"+stu.getComputer().getSerialnum());
*/
/*添加
* StudentDao dao=new StudentDao();
* Student s1=new Student("lwc",21);
Computer c1=new Computer("222","ibm");
s1.setComputer(c1);
c1.setStudent(s1);
dao.insert(s1);
*/
/*删除
* StudentDao dao=new StudentDao();
* Student stu=dao.queryBySid2(2);
dao.delete(stu);*/
JdbcUtil.getConnection().close();
}}自学学得头晕,已经不知道南北了,往大家指点。讲思路也行。我这程序写对了吗?
解决方案 »
- 急寻java压缩算法
- 请教一个thread问题。。。很奇怪。
- 如果没有try-catch-final, 只有throw-catch,那会怎么样?
- 程序编译通过了,运行不起来,大家给点建议
- 了解SOAP的进来看一下
- 连接db2数据库报错:打开套接字时出错。
- swing和swt哪个作出来的界面更好看
- 我的第一次JAVA编程,有点问题
- 朋友帮个忙::在JTree中用model.removeNodeFromParent和model.reload()两个方法为何都没有用??要设置什么或者其它???
- Cannot infer type arguments for TreeSet<> 报这个错是怎么回事呢?
- dwr怎么配置环境
- (2 & 6) >>> 24
private Connection conn=null;
public Database (){
try{
getConn();
}catch(Exception e){
System.out.println("连接数据库异常....."+e.getMessage());
}
}
public void getConn() throws SQLException{
String url ="jdbc:oracle:thin:test/test@localhost:1521:orcl";
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
conn = ods.getConnection();
System.out.println("获得连接对象");
}
//查询结果
public ResultSet getRS(String sql){
Statement stmt=null;
ResultSet rs = null;
try{
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}catch(Exception e){
System.out.println("查询语句失败:"+e.getMessage()+" the sql is : "+sql);
}
return rs;
}
//更新数据
public void execUpdate(String sql){
Statement stmt = null;
try{
stmt = conn.createStatement();
stmt.executeUpdate(sql);
stmt.close();
}catch(Exception e){
System.out.println("更新语句失败:"+e.getMessage()+" the sql is : "+sql);
}
}
public void closeRS(ResultSet rs){
try{
if(rs!=null){
rs.getStatement().close();
rs.close();
rs=null;
}
}catch(Exception e){
e.printStackTrace();
}
}
public void cleanup(){
try{
if(this.conn != null){
if(!this.conn.isClosed()){
this.conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}
}
}
此例中只用了Statement对象,当然也可以用PreparedStatement对象,用PreparedStatement对象时,传参数可以用集合或Object[],这样填充参数时就可以用集合对象了。这样你调用时,只要创建一个Database对象(创建时,调用创建连接的方法),然后传递一个sql语句就可以,然后关闭资源。
你这样写的代码是有问题的!在高并发的环境下,会导致很多的 Connection 对象得不到关闭,在 CSDN 很多的问题是关于连接池用不了多久就会满掉的问题,大多数都是由于这个原因所造成的。如果一定要设为成员变量的话,那必须在设计上考虑很多的因素,并且考虑并发的同步问题,这样做是很复杂的,作为应用程序的开发人员很难进行控制。因此再重申一下我在这里说过不下十次的话:不要把数据库连接对象(Connection, PreparedStatement, ResultSet)设为成员变量,老老实实地将他们改为局部变量。
另外,代码很长,复用度不高,不符合基于接口编程的原则
这个通常在service完成,所白了dao就操作和数据库交互
你给什么我就接什么,在给数据库
你可以在service中维护,就按你的写法,
你直接从service中掉一次insert,在掉update推荐学习下hibernate,
好处:你只需要调update,就给你完成了insert在update另一张表