MySQL jdbc数据库连接

这里用的是MyEclipse工具。

1、准备好MySQL的jar包mysql-connector-java-5.1.7-bin.jar，并添加到项目中；

2、准备好数据库

 

3、在DB Browser中添加数据库

 

4、添加数据库后，编写代码连接数据库

下面写一些例子  查询表的例子

 

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlJDBCTest01 {
/**
* MySQL JDBC原始数据库连接
* @param args
*/
public static void main(String[] args) {
//1、准备数据库连接信息
String user = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";

Connection conn = null;
Statement cs = null;
ResultSet rs = null;
try {
//2、注册驱动
Class.forName(driver);
System.out.println("驱动注册成功！！！");

//连接数据库
conn = DriverManager.getConnection(url, user, password);
System.out.println("数据库连接成功！！！");

//准备sql语句
String sql = "select * from employee";

//编译sql语句
cs = conn.createStatement();

//执行sql语句
rs = cs.executeQuery(sql);

//获取数据库的数据
while(rs.next()){
//获取数据库employee表的第一个字段
int empId = rs.getInt(1);
//获取数据库employee表的第二个字段
String empName = rs.getString(2);
//获取数据库employee表的第三个字段
String empAge = rs.getString("emp_age");
int age = Integer.parseInt(empAge);

System.out.println(empId + " : " + empName + " : " + age);
}

} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("驱动注册失败！！！");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库连接失败！！！");
}finally{
try {
//关闭资源
rs.close();
cs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}
}

 

5、测试的结果是：

 

 

 

插入数据的例子

 

package com.bsuc;

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

 

public class test4 {
public static void main(String[] args) {
// 准备数据库连接信息
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";

Connection conn = null;
try {
//注册驱动
Class.forName(driver);
//连接数据库
conn = DriverManager.getConnection(url, username, password);

//准备数据库语句
//插入数据
//String sql = "insert into employee(empName,empAge) values (?,?)";

//更新数据
String sql = "update employee set empName = ?,empAge = ? where empName = '博' and empAge = 22";

// 删除数据
//String sql = "delete from employee where empName in(?,?)";

//编译SQL语句 如果有占位符? 要用conn.prepareStatement(sql);来编译
PreparedStatement ps = conn.prepareStatement(sql);
//给通配符?赋值 1 代表第一个占位符 2代表第二个占位符 
ps.setString(1, "aa");
ps.setInt(2, 18);

//执行SQL语句 如果不是查询 就用 ps.executeUpdate(); 查询可以用ps.executeQuery();
ps.executeUpdate();

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

 

 

　　多表关联插入

 

package com.jdbc;

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.Scanner;

public class SQLJDBCTest02 {

/**
* 多表关联插入
* 注意事务一致性
* @param args
*/
public static void main(String[] args) {
//准备数据库连接信息
String user = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";

Connection conn = null;
int key = 0;
try {
//注册驱动
Class.forName(driver);

//连接数据库
conn = DriverManager.getConnection(url, user, password);
//关闭自动提交
conn.setAutoCommit(false);

Scanner s = new Scanner(System.in);
System.out.println("请输入部门名称！！！");
String depName = s.next();

System.out.println("请输入员工名称！！！");
String empName = s.next();

//准备sql语句
String sql1 = "insert into department (dep_name) value (?)";
String sql2 = "insert into employee (emp_name,dep_id) value (?,?)";

//编译sql语句
PreparedStatement ps = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);

//给占位符赋值
ps.setString(1, depName);

//执行sql语句
ps.executeUpdate();

//获取返回的主键集 返回的可能不止一个主键
ResultSet keys = ps.getGeneratedKeys();
while(keys.next()){
//获取主键
key = keys.getInt(1);
System.out.println(key);
}

//编译SQL2语句
PreparedStatement ps2 = conn.prepareStatement(sql2);

//给占位符赋值
ps2.setString(1, empName);
ps2.setInt(2, key);

//执行sql2 语句
ps2.executeUpdate();

//手动提交
conn.commit();
} catch (ClassNotFoundException e) {
try {
//数据回滚
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}


}
}