java中操作mysql_在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 show() {

ArrayList list = new

ArrayList();

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 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 V = null;

V = new

Vector();

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，不许用分号结束)