Java例16151623及例171174.docx
《Java例16151623及例171174.docx》由会员分享,可在线阅读,更多相关《Java例16151623及例171174.docx(36页珍藏版)》请在冰点文库上搜索。
Java例16151623及例171174
1、实验目的
1.掌握SQL的相关语言,了解数据库、数据库管理系统和数据库系统的概念,并能熟练使用MYSQL。
2.熟练掌握JAVA连接数据库的代码编写并能用代码对数据库进行相关的操作。
3.学会用JAVA小程序访问数据库。
4.掌握相关的JAVA网络编程语言并能进行相关的通信程序编写。
二、实验内容
(1)完成教材第16章例16.15至例16.23(仔细分析程序并写上详细注释)
(2)完成教材第17章例17.1至例17.4(仔细分析程序并写上详细注释)
三、实验步骤
//例16.15:
编程实现连接到本地数据库StudentSource,然后显示Student表中计算机系的全部学生的学号、姓名、性别和年龄
importjava.sql.*;
publicclassApp16_15
{
privatestaticStringdriver="com.mysql.jdbc.Driver";
privatestaticStringurl="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
java.sql.Connectionconn=null;
java.sql.Statementstmt=null;
ResultSetrs=null;
try
{
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
Stringsql="SELECTsNo,sName,sex,age,deptFROMStudentWHEREdept='计算机'";
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next())
{
Stringno=rs.getString("sNo");
Stringname=rs.getString("sName");
Stringsex=rs.getString("sex");
intage=rs.getInt("age");
Stringdept=rs.getString("dept");
System.out.println(no+""+name+""+sex+""+age+""+dept);
}
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)rs.close();
if(stmt!
=null)stmt.close();
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
//例16.16:
使用statement接口,实现对数据库StudentScore中Student表的查询、添加、修改和删除等操作
importjava.sql.*;
publicclassApp16_16
{
privatestaticStringdriver="com.mysql.jdbc.Driver";
privatestaticStringurl="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
StringselectSql="select*fromStudentwheredept='计算机'";
StringinsertSql="insertintoStudent(sNo,sName,sex,dept)"+"values('201201009','王毅',18,'外语');";
StringupdateSql="updateStudentsetdept='金融'wheresNo='201201009'";
StringdeleteSql="deletefromStudentwheresNo='201201009'";
try
{
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
rs=stmt.executeQuery(selectSql);
while(rs.next())
{
Stringno=rs.getString("sNo");
Stringname=rs.getString("sName");
Stringsex=rs.getString("sex");
intage=rs.getInt("age");
Stringdept=rs.getString("dept");
System.out.println(no+""+name+""+sex+""+age+""+dept);
}
intcount=stmt.executeUpdate(insertSql);
System.out.println("添加了"+count+"条记录到Student表中");
count=stmt.executeUpdate(updateSql);
System.out.println("修改了Student表中"+count+"条记录");
count=stmt.executeUpdate(deleteSql);
System.out.println("删除了Student表中"+count+"条记录");
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)rs.close();
if(stmt!
=null)stmt.close();
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
//例16,17:
使用PreparedStatement接口,实现对表单的查询、添加、修改和删除操作
importjava.sql.*;
publicclassApp16_17
{
privatestaticStringdriver="com.mysql.jdbc.Driver";
privatestaticStringurl="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
PreparedStatementps=null;
ResultSetrs=null;
StringselectSql="SELECT*FROMStudentWHEREdept=?
";
StringinsertSql="INSERTINTOStudent(sNo,sName,sex,age,dept)VALUCES(?
?
?
?
?
);";
StringupdateSql="UPDATEStudentSETdept='金融'WHEREsNo=?
";
StringdeleteSql="DELETEFROMStudentWHEREsNo=?
";
try
{
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
ps=conn.prepareStatement(selectSql);
ps.setString(1,"计算机");
rs=ps.executeQuery();
while(rs.next())
{
Stringno=rs.getString("sNo");
Stringname=rs.getString("sName");
Stringsex=rs.getString("sex");
intage=rs.getInt("age");
Stringdept=rs.getString("dept");
System.out.println(no+""+name+""+sex+""+age+""+dept);
}
ps=conn.prepareStatement(insertSql);
ps.setString(1,"201201009");
ps.setString(2,"王毅");
ps.setString(3,"男");
ps.setInt(4,18);
ps.setString(5,"外语");
intcount=ps.executeUpdate();
System.out.println("添加了"+count+"条记录到Student表中");
ps=conn.prepareStatement(updateSql);
ps.setString(1,"201301009");
count=ps.executeUpdate();
System.out.println("删除了Student表的"+count+"条记录");
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)rs.close();
if(ps!
=null)ps.close();
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
//filenameApp16_18.java使用CallableStatement实现对数据库StudentScore中的Student表的各种存储过程的调用
importjava.sql.*;
publicclassApp16_18
{
privatestaticStringDriver="com.mysql.jdbc.Driver";
privatestaticStringURL="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
CallableStatementcs=null;
ResultSetrs=null;
StringcallSql1="{callladdStudent(?
?
?
?
?
)}";//?
为存储过程IN型参数的占位符
StringcallSql2="{callgetCount(?
)}";//?
为存储过程OUT型参数的占位符
StringcallSql3="{calladdSub(?
?
)}";
try
{
Class.forName(Driver);
conn=DriverManager.getConnection(URL,user,password);
cs=conn.prepareCall(callSql1);//创建用于执行存储过程的对象cs
cs.setString(1,"201202009");//对第14行中的第一个IN参数赋值
cs.setString(2,"王毅");
cs.setString(3,"男");
cs.setInt(4,18);
cs.setString(5,"外语");
cs.execute();//执行SQL存储过程addStudent
cs=conn.prepareCall(callSql2);//创建用于执行存储过程的对象cs
cs.registerOutParameter(1,java.sql.Types.INTEGER);//注册getCount存储过程OUT参数的类型
cs.execute();
inttotal=cs.getInt
(1);//返回存储过程getCount的第1个OUT参数
System.out.println("总人数为:
"+total);
inta=5;
intb=3;
cs=conn.prepareCall(callSql3);
cs.setInt(1,a);
cs.setInt(2,b);
cs.registerOutParameter(1,java.sql.Types.INTEGER);//注册addSub第一个INOUT参数的类型
cs.registerOutParameter(2,java.sql.Types.INTEGER);//注册addSub第二个INOUT参数的类型
cs.execute();
intsum=cs.getInt
(1);
intsub=cs.getInt
(2);
System.out.println(a+"与"+b+"的和:
"+sum+",差:
"+sub);
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(cs!
=null)cs.close();
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
//filenameApp16_19.java使用DatebaseMetaDate获取当前数据库StyudentScore连接的相关信息
importjava.sql.*;
publicclassApp16_19
{
privatestaticStringDriver="com.mysql.jdbc.Driver";
privatestaticStringURL="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
try
{
Class.forName(Driver);
conn=DriverManager.getConnection(URL,user,password);
DatabaseMetaDatadmd=conn.getMetaData();//创建所连接的数据库的元数据对象dmd
System.out.println("数据库产品:
"+dmd.getDatabaseProductName());//返回数据库名
System.out.println("数据库版本:
"+dmd.getDatabaseProductVersion());//返回数据库版本
System.out.println("驱动程序:
"+dmd.getDriverName());//返回驱动程序名
System.out.println("数据库URL:
"+dmd.getURL());//返回数据库的URL
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
//filenameApp16_20.java对当前数据库StudentScore中表Student进行查询后,使用ResultSetMetaData获取当前结果集的相关信息。
importjava.sql.*;
publicclassApp16_20
{
privatestaticStringDriver="com.mysql.jdbc.Driver";
privatestaticStringURL="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
try
{
Class.forName(Driver);
conn=DriverManager.getConnection(URL,user,password);
Stringsql="SELECT*FROMStudentWHEREdept='计算机'";
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);//执行SQL语句
ResultSetMetaDatarsMetaData=rs.getMetaData();//创建结果集结构对象rsMetaData
System.out.println("总共有:
"+rsMetaData.getColumnCount()+"列");//输出结果集的字段数
for(inti=1;i<=rsMetaData.getColumnCount();i++)//利用循环输出结果集的结构信息
{
System.out.println("列"+i+":
"+rsMetaData.getColumnName(i)+","+rsMetaData.getColumnTypeName(i)+"("+rsMetaData.getColumnDisplaySize(i)+")");
}
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)rs.close();
if(stmt!
=null)stmt.close();
if(conn!
=null)conn.close();
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
例16.21通过数据库StudentScore中学生表Student更新操作演示在JDBC中的事物控制。
源代码
//filenameApp16_21.java
importjava.sql.*;
publicclassApp16_21
{
privatestaticStringDriver="com.mysql.jdbc.Driver";
privatestaticStringURL="jdbc:
mysql:
//localhost/StudentScore";
privatestaticStringuser="root";
privatestaticStringpassword="0814";
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
StringselectSql1="INSERTINTOStudent(sNo,sName,sex,age,dept)"+"VALUES('201201010','张三','男','18','计算机');";
StringselectSql2="INSERTINTOStudent(sNo,sName,sex,age,dept)"+"VALUES('201201011','李四','女','19','会计');";
StringselectSql3="INSERTINTOStudent(sNo,sName,sex,age,dept)"+"VALUES('201201007','王五','男','20','金融');";
try
{
Class.f