java.JDBC编程（连接oracle与mySQL数据库）

一、连接oracle数据库：

url =  "jdbc:oracle:thin:@localhost:1521/orcl" 

user = "user1" //创建的用户名（在oracle中以sys创建的用户，相应查询的表也在其中)

password = "123" //用户身份鉴别，即创建用户时设置的密码

加载数据库的驱动："oracle.jdbc.driver.OracleDriver" 

二、连接mysql数据：

url =  "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC" 

user = "root" 

password = "123" 

加载数据库的驱动："com.mysql.cj.jdbc.Driver"

例题1.查询员工信息

员工信息管理。使用Navicat创建test数据库和tb_employee表，包含员工信息如下表所示：

员工信息表（tb_employee）

uid	uname	Sex	salary
1002	Tom	Male	8000
1003	Mary	Female	7500
1004	Peter	Male	5000
1005	John	Male	6000
1006	Sarah	Female	5240

（1）查找uid为1002的员工的username和salary，并输出到控制台；

（2）给定员工信息为：uid：1007，username: Cindy，Sex: Female, salary: 5700，将该用户信息添加到tb_employee表中；

（3）列出低于所有职工平均工资的男性（Male）员工uid和uname；

（4）删除uid为1007的员工信息。

代码实现：

//oracle下代码实现：

import java.sql.*;

public class test {
	public static void main(String []args)
	{
		Connection con=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");//加载oracle数据驱动
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl","admin1","123456");
			Statement statment=con.createStatement();
			String s1="select uname,salary from tb_employee where idd=1002";
			String s2="insert into tb_employee values('1007','Cindy','Female',5700)";
			String s3="select idd,uname from tb_employee o where salary>(select avg(salary) from tb_employee) and Sex='Male'";
			String s4="delete from tb_employee where idd='1007'";
			ResultSet resultset=statment.executeQuery(s1);
			while(resultset.next())
			{
				System.out.println("名字："+resultset.getObject("uname"));
				System.out.println("工资："+resultset.getObject("salary"));
			}
			resultset.close();
			statment.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	

}


//mysql下代码实现：

public class test {
	public static void main(String []args)
	{
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC","root","123");
			Statement statment=con.createStatement();
			String s1="select uname,salary from tb_employee where idd=1002";
			String s2="insert into tb_employee values('1007','Cindy','Female',5700)";
			String s3="select idd,uname from tb_employee o where salary>(select avg(salary) from tb_employee) and Sex='Male'";
			String s4="delete from tb_employee where idd='1007'";
			ResultSet resultset=statment.executeQuery(s1);
			while(resultset.next())
			{
				System.out.println("名字："+resultset.getObject("uname"));
				System.out.println("工资："+resultset.getObject("salary"));
			}
			resultset.close();
			statment.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

例题2：汽车信息查询

在Mysql数据库中建立汽车产品信息表(product)，内容如下：

ProductID	Name	Price	Date
A001	BMW 320	290000	2014-11-03
B002	Benz 200	340000	2009-12-09
C003	Audi A6	350000	2014-09-10
D004	Volkswagen Polo	270000	2011-01-09

  编写程序实现如图1查询界面。

图1

图2

按照“ProductID”（商品编号）进行查询：输入“A001”，点击“查询”按钮，显示如图2所示：

点击“清除界面字符”之后，效果显示如图1。

代码实现：

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.*;

public class frame extends JFrame implements ActionListener{
	JTextField bianhao=new JTextField(1);
	JTextField name=new JTextField(1);
	JTextField price=new JTextField(1);
	JTextField date=new JTextField(1);
	JButton btn1=new JButton("查询");
	JButton btn2=new JButton("清除界面字符");
	Box baseBox,box1,box2;
	frame(){
		baseBox=Box.createHorizontalBox();
		box1=Box.createVerticalBox();
		box1.add(new JLabel("商品编号：") );
		box1.add(Box.createVerticalStrut(30));
		box1.add(btn1);
		btn1.addActionListener(this);
		box1.add(Box.createVerticalStrut(30));
		box1.add(new JLabel("商品名称："));
		box1.add(Box.createVerticalStrut(30));
		box1.add(new JLabel("价格："));
		box1.add(Box.createVerticalStrut(30));
		box1.add(new JLabel("出厂日期："));
		
		box2=Box.createVerticalBox();
		box2.add(bianhao);
		box2.add(Box.createVerticalStrut(25));
		box2.add(btn2);
		btn2.addActionListener(this);
		box2.add(Box.createVerticalStrut(25));
		box2.add(name);
		box2.add(Box.createVerticalStrut(8));
		box2.add(price);
		box2.add(Box.createVerticalStrut(8));
		box2.add(date);
		
		baseBox.add(box1);
		baseBox.add(Box.createHorizontalStrut(10));
		baseBox.add(box2);
		add(baseBox);
		setTitle("汽车信息查询");
		setBounds(100,100,400,300);
		setVisible(true);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	public void actionPerformed(ActionEvent e)
	{
		if(e.getSource()==btn1)
		{
		Connection con=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		try {
			con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl","admin1","123456");
			Statement statment=con.createStatement();
			String s0=bianhao.getText();
			String s1="select * from product where ProductID="+"'"+s0+"'";
			//这个地方注意查询语句书写 ...'s0'...
			
			ResultSet resultset=statment.executeQuery(s1);
			while(resultset.next())
			{
				name.setText(""+resultset.getObject("PName"));
				price.setText(""+resultset.getObject("price"));
				date.setText(""+resultset.getObject("PDate"));
			}
			resultset.close();
			statment.close();
			con.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	   }
		else if(e.getSource()==btn2)
		{
			bianhao.setText("");
			name.setText("");
			price.setText("");
			date.setText("");
		}
	}
	public static void main(String[] argc)
	{
		new frame();
	}
	
}

欢迎批评指正！