文档库

最新最全的文档下载
当前位置:文档库 > Web数据库学生实验报告JDBC部分

Web数据库学生实验报告JDBC部分

Web数据库技术

学生实验报告

院系:信息科学与技术学院

专业:信息管理与信息系统

班级:信A1321/22

任课教师:张海

实验报告(一)

Web数据库学生实验报告JDBC部分

1、请设计一个工程类通过配置文件如下db.properties来获得数据库连接的相关信息,并通过

该配置文件获得数据库连接对象。

db.properties

driver=org.gjt.mm.mysql.Driver

url=jdbc\:mysql\://127.0.0.1/\u5B66\u751F\u5E93

userName=admin

pwd=admin

public class connectionFactory {

public static Connection getConnection() throws SQLException{

}

}

请把getConnection()方法补全。要求设计合理规范,必须有截图。

答案:

public class connectionFactory {

private static Properties pros = new Properties() ;

private static String driver ;

private static String url ;

private static String userName ;

private static String pwd ;

static {

InputStream is =

connectionFactory.class.getClassLoader().getResourceAsStre am("db.properties") ;

try {

pros.load(is) ;

driver = pros.getProperty("driver") ;

url = pros.getProperty("url") ;

user = pros.getProperty("userName") ;

pwd = pros.getProperty("pwd") ;

Class.forName(driver) ;

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static Connection getConnection() throws SQLException{

Connection conn = null ;

try {

conn = DriverManager.getConnection(url, user, pwd) ;

conn.setAutoCommit(false) ;

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn ;

}

}

2、已知学生定义如下:

public class student {

private int id;

private String stuId;

private String name;

private String domCard;//楼栋宿舍号“31-507”

private String bedNo;//床铺号

public student(String stuId, String name, String domCard, String bedNo) {

super();

this.stuId = stuId;

http://www.wendangku.net/doc/b66b8c8b4431b90d6d85c70e.html = name;

this.domCard = domCard;

this.bedNo = bedNo;

}

public String toString(){

return "id="+id+";学号="+stuId+”;姓名=”+name+”;宿舍号=”+domCard+”;床铺号=”+bedNo;

}

//相应get、set方法省略

}

有一

class studentDatas{

public static ArrayList students=new

ArrayList();

static{

student stu=new student("会计A001121","张三","31栋908",1);

students.add(stu);

stu=new student("会计A001166","李四","31栋908",2);

students.add(stu);

stu=new student("会计A001177","王五","31栋807",4);

students.add(stu);

}

}

现要求

(1)根据student类建立一个学生表用来保存student类的相关属性。

Web数据库学生实验报告JDBC部分

(2)通过jdbc,将studentDatas的students集合中的所有学生保持到学生表中;

public class StuAdd {

/**

* @param args

* @throws SQLException

*/

public static void main(String[] args) throws SQLException {

studentDatas stus = new studentDatas() ;

Connection conn = connectionFactory.getConnection() ;

PreparedStatement prStatement = null ;

try {

Web数据库学生实验报告JDBC部分

for

//增加

String sql="insert into lianxi(学号,姓名,宿舍号,床

铺号) values(?,?,?,?)";

prStatement = conn.prepareStatement(sql) ;

prStatement.setString(1, stu.getStuId()) ;

prStatement.setString(2, stu.getName()) ;

prStatement.setString(3, stu.getDomCard()) ;

prStatement.setString(4, stu.getBedNo()) ;

prStatement.executeUpdate();

http://www.wendangku.net/doc/b66b8c8b4431b90d6d85c70e.htmlmit() ;

}

} catch (Exception e) {

try {

conn.rollback() ;

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if (prStatement != null) {

try {

prStatement.cancel() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

class studentDatas{

public static ArrayList students=new

ArrayList();

static{

student stu=new student("会计A001121","张三","31栋

908","1");

students.add(stu);

stu=new student("会计A001166","李四","31栋908","2");

students.add(stu);

stu=new student("会计A001177","王五","31栋807","4");

students.add(stu);

}

}

(3)通过jdbc,将学生表中所有的宿舍是” 31栋908”学生全部调整到“20栋371”

宿舍;

public class StuAlter {

/**

* @param args

* @throws SQLException

*/

public static void main(String[] args) throws SQLException {

// TODO Auto-generated method stub

Connection conn = connectionFactory.getConnection() ;

PreparedStatement prStatement = null ;

try {

//修改

String sql = "update lianxi set 宿舍号 =? where 宿舍号=?" ;

prStatement = conn.prepareStatement(sql) ;

prStatement.setString(1, "20栋371") ;

prStatement.setString(2, "31栋908") ;

prStatement.executeUpdate() ;

http://www.wendangku.net/doc/b66b8c8b4431b90d6d85c70e.htmlmit() ;

} catch (Exception e) {

try {

conn.rollback() ;

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if (prStatement != null) {

try {

prStatement.cancel() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

(4)通过jdbc,删除"31栋807"床铺号是4的学生。

public class StuDeletion {

/**

* @param args

* @throws SQLException

*/

public static void main(String[] args) throws SQLException {

// TODO Auto-generated method stub

List students=new ArrayList();

Connection conn = connectionFactory.getConnection() ;

ResultSet rs = null ;

PreparedStatement prStatement = null ;

try {

//删除

String sql = "delete from lianxi where 宿舍号='31栋807' and 床铺号='4'" ;

prStatement=conn.prepareStatement(sql);

prStatement.executeUpdate();

http://www.wendangku.net/doc/b66b8c8b4431b90d6d85c70e.htmlmit() ;

} catch (Exception e) {

try {

conn.rollback() ;

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if (prStatement != null) {

try {

prStatement.cancel() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

3、ResultSet对象操作

请将上题中宿舍号最后一位是”8”的学生信息全部显示出来。

public class StuQuery {

/**

* @param args

* @throws SQLException

*/

public static void main(String[] args) throws SQLException {

// TODO Auto-generated method stub

List students=new ArrayList();

Connection conn = connectionFactory.getConnection() ;

ResultSet rs = null ;

PreparedStatement prStatement = null ;

student stu = null ;

try {

String sql = "select lianxi.* from lianxi where 宿舍号 like '%8'" ;

prStatement = conn.prepareStatement(sql) ;

rs = prStatement.executeQuery(sql) ;

while (rs.next()) {

stu=new student(sql, sql, sql, sql);

stu.setId(rs.getInt("id"));

stu.setStuId(rs.getString("学号"));

stu.setName(rs.getString("姓名"));

stu.setDomCard(rs.getString("宿舍号"));

stu.setBedNo(rs.getString("床铺号"));

students.add(stu);

System.out.println(stu);

}

} catch (Exception e) {

try {

conn.rollback() ;

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if (prStatement != null) {

try {

prStatement.cancel() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close() ;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

4、请用perparedStatement对象保存如下:

student stu=new student("信息A110099","刘六","31栋818",8);

student对象的实例stu到数据中。

答:同第一问将studentDatas stus = new studentDatas() ;改为student stu=new student("信息A110099","刘六","31栋818",8);

去掉循环即可;

注:打印太多,兴趣题没有打印