在JAVA程序中操作MySQL数据库的演示代码

1.java中mysql数据库的使用

package com.TEL;

public class Tel {
 //属性
 private String uid;
 private String uname;
 private String pno;
 //属性的GetXX()和SetXX()方法
 public String getUid() {
  return uid;
 }
 public void setUid(String uid) {
  this.uid = uid;
 }
 public String getUname() {
  return uname;
 }
 public void setUname(String uname) {
  this.uname = uname;
 }
 public String getPno() {
  return pno;
 }
 public void setPno(String pno) {
  this.pno = pno;
 }
 Tel(String uid,String uname,String pno){
  this.uid=uid;
  this.uname=uname;
  this.pno=pno;
 }
}

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

public class TelDAO {
 String driver = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://localhost:3306/test";
 String username = "root";
 String password = "1234";
 //定义数据库操作对象
 Connection conn = null;
 Statement st;
 PreparedStatement PS;
 ResultSet RS;
 TelDAO(){
  try {
   Class.forName(driver).newInstance();
   conn=DriverManager.getConnection(url,username,password);
   st=conn.createStatement();
   //建表
   String sCreate="create table if not exists block(id varchar(10) primary key,title varchar(50),context varchar(200))";
   st.addBatch(sCreate);
   st.executeBatch();
   st.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 //插入
 public void insert(String id, String name, String phno) {
  String str = "select * from users order by id desc";   
  String sInsert = "insert into users(id,name,phno)values(?,?,?)";
  try {
   PS = conn.prepareStatement(str);
   RS = PS.executeQuery();
   while (RS.next()) {
    if (phno.equals(RS.getString(3))) {
     JOptionPane.showMessageDialog(null, "电话号码相同，请重新输入");
     return;
    }
   }   
   PS = conn.prepareStatement(sInsert);
   PS.setString(1, id);
   PS.setString(2, name);
   PS.setString(3, phno);
   PS.execute();
   PS.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 // 修改数据
 public void SU(Tel t, String str) {
  String sUpdate = "update users set id=?,name=?,phno=?  where id=?";
  try {
   PS = conn.prepareStatement(sUpdate);
   PS.setString(1, t.getUid());
   PS.setString(2, t.getUname());
   PS.setString(3, t.getPno());
   PS.setString(4, str);
   PS.execute();
   PS.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 // 显示表中数据
 public List<Tel> show() {
  ArrayList<Tel> list = new ArrayList<Tel>();
  String sSelect = "select * from users order by id desc";
  try {
   PS = conn.prepareStatement(sSelect);
   RS = PS.executeQuery();
   while (RS.next()) {
    Tel t = new Tel(RS.getString(1),RS.getString(2),RS.getString(3));
    list.add(t);
   }
   PS.close();
   RS.close();
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return list;
 }
 
 // 删除数据
 public void SD(String str) {
  String sDelete = "delete from users where id=?";
  try {
   PS = conn.prepareStatement(sDelete);
   PS.setString(1, str);
   PS.execute();
   PS.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}

import java.awt.Container;
import java.awt.Panel;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.util.List;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class TelFrame extends JFrame implements ActionListener{

 
 private static final long serialVersionUID = 1L;
 
 JTable jtTable;
 JLabel jlID, jlName, jlPhno;
 JTextField jtID, jtName, jtPhno;
 JButton jbInsert, jbCancel, jbDel, jbUpdate;
 DefaultTableModel DTM;
 List<Tel> LT;
 
 public TelFrame(){
  super("数据库连接");
  setSize(600, 450);
  setDefaultCloseOperation(EXIT_ON_CLOSE);

  Container container = getContentPane();
  JScrollPane scrollPane = new JScrollPane();//滚动窗格
  scrollPane.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
  scrollPane.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
  Panel panel = new Panel();
  panel.setLayout(null);
  container.add(panel);
  
  
  DTM = new DefaultTableModel();
  
  //将"编号"、"姓名"、"电话号码"列添加到表模型中
  DTM.addColumn("编号");
  DTM.addColumn("姓名");
  DTM.addColumn("电话号码");
  
  jtTable = new JTable();
  jlID = new JLabel("编号");
  jlName = new JLabel("姓名");
  jlPhno = new JLabel("电话号码");
  
  jtID = new JTextField();
  jtName = new JTextField();
  jtPhno = new JTextField();
  jbInsert = new JButton("添加");
  jbCancel = new JButton("退出");
  jbDel = new JButton("删除");
  jbUpdate = new JButton("修改");
  
  jlID.setBounds(50, 30, 100, 25);
  jlName.setBounds(250, 30, 100, 25);
  jlPhno.setBounds(50, 80, 100, 25);
  
  jtID.setBounds(80, 30, 150, 25);
  jtName.setBounds(280, 30, 150, 25);
  jtPhno.setBounds(110, 80, 150, 25);
  
  jtTable.setBounds(50, 120, 400, 200);
  scrollPane.setBounds(50, 120, 400, 200);
  jbInsert.setBounds(250, 350, 80, 25);
  jbCancel.setBounds(350, 350, 80, 25);
  jbDel.setBounds(150, 350, 80, 25);
  jbUpdate.setBounds(50, 350, 80, 25);
  
  jbInsert.addActionListener(this);
  jbCancel.addActionListener(this);
  jbDel.addActionListener(this);
  jbUpdate.addActionListener(this);
  
  panel.add(jlID);
  panel.add(jlName);
  panel.add(jlPhno);
  
  panel.add(jtID);
  panel.add(jtName);
  panel.add(jtPhno);
  
  panel.add(jbInsert);
  panel.add(jbCancel);
  panel.add(jbDel);
  panel.add(jbUpdate);
  
  jtTable.setModel(DTM);
  jtTable.addMouseListener(new MouseAdapter() {
   public void mouseClicked(MouseEvent event) {
    int row = jtTable.getSelectedRow();
    jtID.setText(DTM.getValueAt(row, 0).toString());
    jtName.setText((String) DTM.getValueAt(row, 1));
    jtPhno.setText(DTM.getValueAt(row, 2).toString());
   }
  });
  
  scrollPane.getViewport().add(jtTable);
  panel.add(scrollPane);

  setVisible(true);
  setLocationRelativeTo(null);

  TelDAO TD = new TelDAO();
  LT = TD.show();
  flush();
 }
 
 // 刷新JTABLE
 public void flush() {
  DTM.getDataVector().clear();
  DTM.fireTableStructureChanged();
  for (Tel t : LT) {
   Vector<Object> V = null;
   V = new Vector<Object>();
   V.addElement(t.getUid());
   V.addElement(t.getUname());
   V.addElement(t.getPno());
   DTM.addRow(V);
  }
  DTM.fireTableStructureChanged();
 }
 
 public void actionPerformed(ActionEvent e) {
  //添加按钮
  if (e.getSource() == jbInsert) {
   TelDAO DAO = new TelDAO();
   DAO.insert(jtID.getText(), jtName.getText(), jtPhno.getText());
   LT= DAO.show();
   flush();
   jtID.setText(null);
   jtName.setText(null);
   jtPhno.setText(null);
  }
  
  // 删除按钮
  else if (e.getSource() == jbDel) {
   String str;
   int row = jtTable.getSelectedRow();
   if (row != -1) {
    TelDAO DAO = new TelDAO();
    str = DTM.getValueAt(row, 0).toString();
    DAO.SD(str);
    LT = DAO.show();
    flush();
    jtID.setText(null);
    jtName.setText(null);
    jtPhno.setText(null);
    
   } else {
    JOptionPane.showConfirmDialog(this, "请选择要删除的行");
   }
  }
  // 修改按钮
  else if (e.getSource() == jbUpdate) {
   String str;
   int row = jtTable.getSelectedRow();
   if (row != -1) {
    TelDAO DAO = new TelDAO();
    str = (String) DTM.getValueAt(row, 0);
    Tel T = new Tel(jtID.getText(),jtName.getText(),jtPhno.getText());
    DAO.SU(T, str);
    LT = DAO.show();
    flush();
    jtID.setText(null);
    jtName.setText(null);
    jtPhno.setText(null);
   } else {
    JOptionPane.showConfirmDialog(this, "请选择要修改的行");
   }
  }
  // 退出按钮
  else if (e.getSource() == jbCancel) {
   System.exit(0);
  }
 }

 public static void main(String[] args) {
  new TelFrame();
 } 
}

结果运行：

2.本人在操作中常用到的mysql数据库命令

使用show 语句找出服务器上当前在什么数据库；

访问test数据库：use test（注意use类似quit，不许用分号结束） 

 

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。