(1) 导入JDBC类;
(2) 装载/注册驱动程序;
(3) 连接数据库;
(4) 创建语句对象;
(5) 执行SQL语句;
(6) 处理结果;
(7) 关闭连接。
import java.sql.*;
public class SimpleDBDemo {
static String strDriver ="com.mysql.cj.jdbc.Driver";
static String strURL = "jdbc:mysql://localhost:3306/mytest?"+"useSSL=false&serverTimezone=GMT&characterEncoding=utf-8";
public static void main(String[] args) {
try {
Class.forName(strDriver);
System.out.println("SQLServerDriver success");
Connection con = DriverManager.getConnection(strURL, "root", "123456");
System.out.println("Connection success");
Statement stm = con.createStatement();
String strSql = "select * from student";
ResultSet rs = stm.executeQuery(strSql);
while(rs.next()){
String s1 = rs.getString(1);
String s2 = rs.getString(2);
Double d3 = rs.getDouble(3);
System.out.println(s1+"\t"+s2+"\t\t"+d3);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
}
}
*建立程序界面*
启动Eclipse,新建一个Java应用程序项目,名称为“StudentManager”。
利用JTabbedPane新建选项卡 一个名为“inputPanel”(录入记录)。另一个名为“viewPanel”(浏览记录)。
在“录入记录”面板上添加一个面板“inputInnerPanel”和一个按钮“inputBtn”,按钮标题为“录入”。
在面板“inputInnerPanel”上添加4个标签和4个文本字段,具体内容如表14.3所示。
表14.3 四个标签和文本字段
*标签名称* | *标签文本* | *文本字段名称* | *文本字段宽度* |
noLabel | 学号 | noField | 10 |
nameLabel | 姓名 | nameField | 10 |
gendarLabel | 性别 | gendarField | 2 |
telLabel | 联系电话 | telField | 11 |
import javax.swing.*;
import java.awt.*;
public class StudentPanel extends JPanel{
//Jlabel实现标签
JLabel snoLabel;//学生学号标签
JLabel snameLabel;//学生姓名标签
JLabel cnoLabel;//课程号标签
JLabel cnameLabel;//课程名标签
JLabel addressLabel;//上课地点标签
JLabel timeLabel;//上课时间标签
‘
//JTextField实现输入框
JTextField snoField;//学生学号输入框
JTextField snameField;//学生姓名输入框
JTextField cnoField;//课程号输入框
JTextField cnameField;//课程名输入框
JTextField addressField;//上课地点输入框
JTextField timeField;//上课时间输入框
public StudentPanel(){
setGUIComponent();
}
public void setGUIComponent(){
//初始化组件
snoLabel = new JLabel("学号");
snoField = new JTextField(10);
snameLabel = new JLabel("姓名");
snameField = new JTextField(10);
cnoLabel = new JLabel("课程号");
cnoField = new JTextField(10);
cnameLabel = new JLabel("课程名");
cnameField = new JTextField(10);
addressLabel = new JLabel("上课地点");
addressField = new JTextField(10);
timeLabel = new JLabel("上课时间");
timeField = new JTextField(10);
//设置组件
//3行4列
GridLayout(3,4);
setLayout(new GridLayout(3,4));//设置排列方式
add(snoLabel);add(snoField);
add(snameLabel);add(snameField);
add(cnoLabel);add(cnoField);
add(cnameLabel);add(cnameField);
add(addressLabel);add(addressField);
add(timeLabel);add(timeField);
}
//清楚文本框的内容
public void cleanContent(){
snoField.setText("");
snameField.setText("");
cnoField.setText("");
cnameField.setText("");
addressField.setText("");
timeField.setText("");
}
setText 与getText 方法获取内容与写入内容
//set() methods
public void setsno(String n){//设置学号文本框内容为n
snoField.setText(n);
}
public void setsname(String name){//设置学生姓名文本框为name
snameField.setText(name);
}
public void setcno(String m){//设置课程号文本框为m
cnoField.setText(m);
}
public void setcname(String cname){//设置课程名文本框为cname
cnameField.setText(cname);
}
public void setaddress(String address){//设置上课地址为address
addressField.setText(address);
}
public void settime(String time){//设置上课时间为time
timeField.setText(time);
}
//get() methods
public String getsno(){//得到学生学号
return snoField.getText();
}
public String getsname(){//得到学生姓名
return snameField.getText();
}
public String getcno(){//得到课程号
return cnoField.getText();
}
public String getcname(){//得到课程名
return cnameField.getText();
}
public String getaddress(){//得到上课地址
return addressField.getText();
}
public String gettime(){//得到上课时间
return timeField.getText();
}
}
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
///数据库连接部分
public class StudentManagement extends JFrame implements ActionListener{
JTabbedPane dbTabPane;//定义选择框
///JTabbedPane实现标签页
//几个界面
JPanel inputPanel;//录入界面
JPanel viewPanel;//浏览界面
JPanel searchPanel;//查询界面
JPanel deletePanel;//删除界面
JButton inputbtn;
StudentPanel inputinnerPanel;//录入组件
JButton viewbtn;
JTextArea viewArea;//浏览组件
JLabel inputnoLabel;//删除组件
JTextField inputnoField;
JButton inputnobtn;
StudentPanel deleteinnerPanel;
JLabel inputnoLabel1;//查询组件
JTextField inputnoField1;
JTextArea viewArea1;
JButton inputnobtn1;
Connection conn;
Statement stmt;
public StudentManagement(){
super("计科081学生选课查询");
setGUIComponent();
}
public void setGUIComponent(){
Container c = getContentPane();
c.setLayout(new BorderLayout());
dbTabPane = new JTabbedPane();
//定义录入面板
inputPanel = new JPanel();
inputPanel.setLayout(new FlowLayout());
inputbtn = new JButton("录入");
inputbtn.addActionListener(this);
inputinnerPanel = new StudentPanel();
inputPanel.add(inputinnerPanel);
inputPanel.add(inputbtn);
dbTabPane.add("录入",inputPanel);
//定义浏览面板
viewPanel = new JPanel();
viewPanel.setLayout(new BorderLayout());
viewArea = new JTextArea(8,50);
viewbtn = new JButton("浏览");
viewPanel.add(new JScrollPane(viewArea),BorderLayout.CENTER);
viewPanel.add(viewbtn,BorderLayout.SOUTH);
viewbtn.addActionListener(this);
dbTabPane.add("浏览",viewPanel);
//定义查询面板
inputnoLabel1 = new JLabel("输入学号");
inputnoField1 = new JTextField(10);
inputnobtn1 = new JButton("查询");
inputnobtn1.addActionListener(this);
viewArea1 = new JTextArea(4,50);
searchPanel = new JPanel();
searchPanel.add(inputnoLabel1);
searchPanel.add(inputnoField1);
searchPanel.add(inputnobtn1);
searchPanel.add(new JScrollPane(viewArea1),BorderLayout.CENTER);
searchPanel.add(new JLabel(" 方正为人 勤慎治学"),BorderLayout.NORTH);
dbTabPane.add("查询",searchPanel);
//定义删除面板
inputnoLabel = new JLabel("输入学号");
inputnoField = new JTextField(10);
deletePanel = new JPanel();
inputnobtn = new JButton("删除");
inputnobtn.addActionListener(this);
deletePanel.add(inputnoLabel);
deletePanel.add(inputnoField);
deletePanel.add(inputnobtn);
dbTabPane.add("删除",deletePanel);
c.add(BorderLayout.NORTH,dbTabPane);
}
public void connection(){//连接数据库
try{
String Driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
Class.forName(Driver);
}catch(ClassNotFoundException e1){
System.err.println("驱动程序加载失败");
}
try{
String url="jdbc:odbc:1";
conn = DriverManager.getConnection(url,"sa","123456");
stmt = conn.createStatement();
}catch(SQLException e2){
e2.getSQLState();
e2.getMessage();
}
}
public void close(){//关闭执行语句和数据库
try{
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
}catch(SQLException e2){
System.err.println("不能正常关闭");
}
}
public void inputRecords(){//录入信息
String sno1 = inputinnerPanel.getsno();//获得学号
String sname1 = inputinnerPanel.getsname();//获得姓名
String cno1 = inputinnerPanel.getcno();//获得课程号
String cname1 = inputinnerPanel.getcname();//获得课程名
String address1 = inputinnerPanel.getaddress();//获得地址
String time1 = inputinnerPanel.gettime();//获得时间
try{
connection();
String insSQL = "";
insSQL = "insert into Student ";
insSQL += "values(?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(insSQL);
pstmt.setString(1, sno1);
pstmt.setString(2, sname1);
pstmt.setString(3, cno1);
pstmt.setString(4, cname1);
pstmt.setString(5, address1);
pstmt.setString(6, time1);
pstmt.executeUpdate();
}catch(SQLException e1){
System.err.println(e1.getSQLState());
}
finally{
close();
}
JOptionPane.showMessageDialog(null, "添加成功");
}
public void viewRecords(){//浏览
try{
String viewString = "";
connection();
String sqlstring ;
sqlstring = "select sNo 学号,sName 姓名,cNo 课程号,cName 课程名,address 上课地点,classtime 上课时间";
sqlstring += "\n";
sqlstring += "from Student";
ResultSet rs = stmt.executeQuery(sqlstring);
ResultSetMetaData rsMeta = rs.getMetaData();
int nums = rsMeta.getColumnCount();
//获得字段名字
for(int i =1;i<=nums;i++){
viewString += rsMeta.getColumnName(i)+"\t";
}
viewString += "\n";
//获得记录
while(rs.next()){
for(int i =1;i<=nums;i++){
viewString += rs.getString(i)+"\t";
}
viewString += "\n";
viewArea.setText(viewString);
}
}catch(SQLException e1){
System.err.println("浏览失败");
System.err.println(e1.getSQLState()+e1.getMessage());
}
finally{
close();
}
}
public void searchRecords(String no){//查询
String n = no;
try{
String searchString;
String viewString1 = "";
connection();
searchString = "select sNo 学号,sName 姓名,cNo 课程号,cName 课程名,address 上课地址,classtime 上课时间 ";
searchString += "from Student ";
searchString += "where sNo = ?";
PreparedStatement pstmt = conn.prepareStatement(searchString);
pstmt.setString(1, n);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsMeta = rs.getMetaData();
int nums = rsMeta.getColumnCount();
//获得字段名字
for(int i =1;i<=nums;i++){
viewString1 += rsMeta.getColumnName(i)+"\t";
}
viewString1 += "\n";
//获得记录
while(rs.next()){
for(int i =1;i<=nums;i++){
viewString1+=rs.getString(i)+"\t";
}
viewString1 += "\n";
viewArea1.setText(viewString1);
}
}catch(SQLException e1){
System.err.println("查询失败");
System.err.println(e1.getSQLState()+e1.getMessage());
}
finally{
close();
}
}
public void deleteRecords(String no){//删除
String n = no;
try{
String deleteString;
connection();
deleteString = "delete from Student ";
deleteString += "where sNo = ?";
PreparedStatement pstmt = conn.prepareStatement(deleteString);
pstmt.setString(1, n);
pstmt.executeUpdate();
}catch(SQLException e1){
System.err.println("操作失败");
System.err.println(e1.getSQLState()+e1.getMessage());
}
finally{
close();
}
JOptionPane.showMessageDialog(null, "删除成功");
}
public void actionPerformed(ActionEvent e){
if(e.getSource() == inputbtn){
inputRecords();
}
else if(e.getSource() == viewbtn){
viewRecords();
}
else if(e.getSource() == inputnobtn){
deleteRecords(inputnoField.getText());
}
else if(e.getSource() == inputnobtn1){
searchRecords(inputnoField1.getText());
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
StudentManagement sm = new StudentManagement();
sm.setSize(600,250);
sm.setVisible(true);
sm.setResizable(false);
sm.setLocation(300,300);
sm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
}