Java第十五章例题源代码
第十五章 Java数据库连接(JDBC)
例子1
import java.sql.*;
public class Example15_1
{ public static void main(String args[])
{ Connection con;
Statement sql;
ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{ System.out.println(""+e);
}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM chengjibiao");
while(rs.next())
{ String number=rs.getString(1);
String name=rs.getString(2);
String date=rs.getString(3);
int math=rs.getInt("math");
int english=rs.getInt("english");
System.out.print("学号："+number);
System.out.print(" 姓名："+name);
System.out.print(" 出生："+date);
System.out.print(" 数学："+math);
System.out.println(" 英语："+english);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子2
import java.sql.*;
public class Example15_2
{ public static void main(String args[])
{ Connection con;
Statement sql;
ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.createStatement();
rs=sql.executeQuery("SELECT name,english FROM chengjibiao WHERE english >= 80 ");
while(rs.next())
{ String name=rs.getString(1);
int english=rs.getInt("english");
System.out.print(" 姓名："+name);
System.out.println(" 英语："+english);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子3
import java.sql.*;
public class Example15_3
{ public static void main(String args[])
{ Connection con;Statement sql; ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{ System.out.println(""+e);
}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=sql.executeQuery("SELECT name,english FROM chengjibiao");
https://www.sodocs.net/doc/fc8253087.html,st();
int number=rs.getRow();
System.out.println("该表共有"+number+"条记录");
rs.afterLast();
while(rs.previous())
{ String name=rs.getString("name");
int english=rs.getInt("english
");
System.out.print(" 姓名："+name);
System.out.println(" 英语："+english);
}
System.out.println("单独输出第5条记录:");
rs.absolute(5);
String name=rs.getString("name");
int english=rs.getInt("english");
System.out.print(" 姓名："+name);
System.out.println(" 英语："+english);
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子4
import java.sql.*;
public class Example15_4
{ public static void main(String args[])
{ Connection con;Statement sql; ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.createStatement();
String condition="SELECT name,english FROM chengjibiao ORDER BY english";
rs=sql.executeQuery(condition);
while(rs.next())
{ String name=rs.getString(1);
int english=rs.getInt(2);
System.out.print(" 姓名："+name);
System.out.println(" 英语："+english);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子5
import java.sql.*;
public class Example15_5
{ public static void main(String args[])
{ Connection con;
Statement sql;
ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.createStatement();
rs=sql.executeQuery("SELECT name,math FROM chengjibiao WHERE name LIKE '%小%' ");
while(rs.next())
{ String name=rs.getString(1);
int math=rs.getInt(2);
System.out.print(" 姓名："+name);
System.out.println(" 数学："+math);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子6
import java.sql.*;
import java.util.LinkedList;
public class Example15_6
{ public static void main(String args[])
{ LinkedList list=new LinkedList();
Connection con;
Statement sql;
ResultSet rs;
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=sql.executeQuery("SELECT name,math FROM chengjibiao");
https://www.sodocs.net/doc/fc8253087.html,st();
int lownumber=rs.getRow();
int number=lownumber;
for(int i=1;i<=number;i++)
{ list.add(new Integer(i));
}
double sum=0;
int k=4;
int 抽取数目=k;
while(k>0)
{ int i=(int)(Math.random()*list.size());
int index=((Integer)list.get(i)).intValue();
rs.absolute(index);
System.out.print("姓名:"+rs.getString(1));
System.out.println("数学:"+rs.getString(2));
int math=rs.getInt("math");
sum=sum+math;
k--;
list.remove(i);
}
System.out.println("抽样的数学平均成绩："+sum/抽取数目);
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子7
import java.sql.*;
public class Example15_7
{ public static void main(String args[])
{ Connection con;
Statement sql;
ResultSet rs;
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.createStatement();
int math=99,english=0;
String number="2003001",date,name,recode,updateStr,insertStr,delStr;
updateStr=
"UPDATE chengjibiao SET math ="+math+" WHERE number = "+"'"+number+"'";
math=92;
english=66;
number="2003039";
name="周王";
date="1999-12-28";
recode= "("+"'"+number+"'"+","+"'"+name+"'"+","+"'"+date+"'"+","+math+","+english+")";
insertStr="INSERT INTO chengjibiao VALUES "+recode;
delStr="DELETE FROM chengjibiao WHERE number = '2003004' ";
sql.executeUpdate(updateStr);
sql.executeUpdate(insertStr);
sql.executeUpdate(delStr);
rs=sql.executeQuery("SELECT * FROM chengjibiao");
while(rs.next())
{ number=rs.getString(1);
name=rs.getString(2);
date=rs.getString(3);
math=rs.getInt("math");
english=rs.getInt("english");
System.out.print("学号："+number);
System.out.print(" 姓名："+name);
System.out.print(" 出生："+date);
System.out.print(" 数学："+math);
System.out.println(" 英语："+english);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子8
import java.sql.*;
public class Example15_8
{ public static void main(String args[])
{ Connection con;
PreparedStatement sql;
ResultSet rs;
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{ System.out.println(""+e);
}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.prepareStatement("SELECT * FROM chengjibiao"); //预处理语句
rs=sql.executeQuery();
while(rs.next())
{ String number=rs.getString(1);
String name=rs.getString(2);
System.out.print("学号："+number);
System.out.println(" 姓名："+name);
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子9
import java.sql.*;
public class Example15_9
{ public static void main(String args[])
{ Connection con;
PreparedStatement sqlOne,sqlTwo,sqlThree;
ResultSet rs;
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sqlOne=
con.prepareStatement("UPDATE chengjibiao SET math = ? WHERE number= ? ");
sqlOne.setInt(1,100);
sqlOne.setString(2,"2003001");
sqlOne.executeUpdate();
sqlOne.setInt(1,99);
sqlOne.setString(2,"2003002");
sqlOne.executeUpdate();
sqlTwo=
con.prepareStatement("INSERT INTO chengjibiao VALUES (?,?,?,?,?)");
sqlTwo.setString(1,"2003888");
sqlTwo.setString(2,"李向阳");
sqlTwo.setString(3,"1919-05-04");
sqlTwo.setInt(4,99);
sqlTwo.setInt(5,88);
sqlTwo.executeUpdate();
sqlThree=con.prepareStatement
("SELECT number,math,english FROM chengjibiao WHERE english >= ? AND math >= ? ");
sqlThree.setInt(1,88);
sqlThree.setInt(2,90);
rs=sqlThree.executeQuery() ;
while(rs.next())
{ System.out.print("学号："+rs.getString(1));
System.out.print(" 数学："+rs.getInt(2));
System.out.println(" 英语："+rs.getInt(3));
}
con.close();
}
catch(SQLException e)
{ System.out.println(e);
}
}
}
例子10
（1） 客户端程序
Client.java
import https://www.sodocs.net/doc/fc8253087.html,.*;
import java.io.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
public class Client
{
public static void main(String args[])
{ new QueryClient();
}
}
class QueryClient extends Frame
implements Runnable,ActionListener
{ Button connection,send;
TextField inputText;
TextArea showResult;
Socket socket=null;
DataInputStream in=null;
DataOutputStream out=null;
Thread thread;
QueryClient()
{ socket=new Socket();
Panel p=new Panel();
connection=new Button("连接服务器");
send=new Button("发送");
send.setEnabled(false);
inputText=new TextField(8);
showResult=new Te
xtArea(6,42);
p.add(connection);
p.add(new Label("输入学号"));
p.add(inputText);
p.add(send);
add(p,BorderLayout.NORTH);
add(showResult,BorderLayout.CENTER);
connection.addActionListener(this);
send.addActionListener(this);
thread=new Thread(this);
setBounds(10,30,350,400);
setVisible(true);
validate();
addWindowListener(new WindowAdapter()
{ public void windowClosing(WindowEvent e)
{ System.exit(0);
}
});
}
public void actionPerformed(ActionEvent e)
{ if(e.getSource()==connection)
{ try
{ if(socket.isConnected())
{}
else
{ InetAddress address=InetAddress.getByName("127.0.0.1");
InetSocketAddress socketAddress=new InetSocketAddress(address,4331);
socket.connect(socketAddress);
in =new DataInputStream(socket.getInputStream());
out = new DataOutputStream(socket.getOutputStream());
send.setEnabled(true);
thread.start();
}
}
catch (IOException ee){}
}
if(e.getSource()==send)
{ String s=inputText.getText();
if(s!=null)
{ try { out.writeUTF(s);
}
catch(IOException e1){}
}
}
}
public void run()
{ String s=null;
while(true)
{ try{ s=in.readUTF();
showResult.append("\n"+s);
}
catch(IOException e)
{ showResult.setText("与服务器已断开");
break;
}
}
}
}
（2） 服务器端程序
Server.java
import java.io.*;
import https://www.sodocs.net/doc/fc8253087.html,.*;
import java.util.*;
import java.sql.*;
public class Server
{ public static void main(String args[])
{ Connection con;
PreparedStatement sql=null;
ResultSet rs;
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try{ con=DriverManager.getConnection("jdbc:odbc:sun","gxy","123");
sql=con.prepareStatement("SELECT * FROM chengjibiao WHERE number = ? ");
}
catch(SQLException e){}
ServerSocket server=null;
Server_thread thread;
Socket you=null;
while(true)
{ try{ server=new ServerSocket(4331);
}
catch(IOException e1)
{ System.out.println("正在监听");
}
try{ System.out.println(" 等待客户呼叫");
you=server.accept();
System.out.println("客户的地址:"+you.getInetAddress());
}
catch(IOException e)
{ System.out.println("正在等待客户");
}
if(you!=null)
{ new Server_thread(you,sql).start();
}
}
}
}
class Server_thread extends Thread
{ Socket socket;
DataOutputStream out=null;
DataInputStream in=null;
PreparedStatement sql;
boolean boo=false;
Server_thread(Socket t, PreparedStatement sql)
{ socket=t;
this.sql=sql;
try { out=new DataOutputStream(socket.getOutputStream());
in=new DataInputStream(socket.getInputStream());
}
catch(IOException e){}
}
public void run()
{ while(true)
{try{
String num=in.readUTF();
boo=false;
sql.setString(1,num);
ResultSet rs=sql.executeQuery() ;
while(rs.next())
{ boo=true;
String number=rs.getString(1);
String name=rs.getString(2);
String date=rs.getString(3);
int math=rs.getInt(4);
int english=rs.getInt(5);
out.writeUTF("学号:"+number+" 姓名:"+name+" 出生:"+date
+" 数学:"+math+" 英语"+english);
}
if(boo==false)
{ out.writeUTF("没有该学号！");
}
}
catch (Exception e)
{ System.out.println("客户离开"+e);
return;
}
}
}
}