什么是JDBC:
java 数据库连接(java database connectibity),是java语言用来规范客户端程序如何来访问数据库的应用程序接口。JDBC是面向关系型数据库的。
简单地说,就是用于执行SQL语句的一类Java API,通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装,可以使开发人员使用纯Java API完成SQL的执行。
准备工作:
安装mysql数据库和navicat for mysql,在navicat中创建需要用到的数据库。使用JDBC需要在工程中导入对应的jar包,在Eclipse下的导入方法:
接着右键项目->build path->add externl.....选择jdbc压缩包。
JDBC对数据库操作流程:
1.创建Connection对象、SQL查询命令字符串;
2.对Connection对象传入SQL查询命令,获得PreparedStatement对象;
3.对PreparedStatement对象执行executeUpdate()或executeQurey()获得结果;
4.先后关闭PreparedStatement对象和connection对象。、
常用到的类有Connection、PreparedStatement、ResultSet类
Connection
与特定的数据库连接。Connection对象的数据库能够提供描述其表、所支持的SQL语法、储存过程、此连接功能等等的信息。
此信息是使用getMetaData方法获得的。
PreparedStatement
表示预编译的SQL语句的对象。
SQL语句被预编译并存储在PreparedStatement对象中,然后可以使用此对象多次高效的执行该语句。
常用方法
boolean execute()
在此PreparedStatement对象中执行SQL语句,该语句可以是任何种类的SQL语句。
ResultSet excuteQuery()
在此 PreparedStatement 对象中执行 SQL 查询,并返回该查询生成的 ResultSet 对象。
int executeUpdate()
在此 PreparedStatement 对象中执行 SQL 语句,该语句必须是一个 SQL 数据操作语言(Data Manipulation Language,DML)语句,比如 INSERT、UPDATE 或 DELETE 语句;或者是无返回内容的 SQL 语句,比如 DDL 语句。
ResultSet
表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。
JDBC基本操作
1.定义记录的类player.java
package JDBCForMysql;
public class Player {
private String Name;
private String password;
private int Id;
Player(String Name,String password){
this.Name = Name;
this.password = password;
}
public int getId(){
return Id;
}
public void setId(int Id){
this.Id = Id;
}
public String getName(){
return Name;
}
public void setName(String Name){
this.Name = Name;
}
public String getpassword(){
return password;
}
public void setpassword(String password){
this.password = password;
}
}
public class Player {
private String Name;
private String password;
private int Id;
Player(String Name,String password){
this.Name = Name;
this.password = password;
}
public int getId(){
return Id;
}
public void setId(int Id){
this.Id = Id;
}
public String getName(){
return Name;
}
public void setName(String Name){
this.Name = Name;
}
public String getpassword(){
return password;
}
public void setpassword(String password){
this.password = password;
}
}
2.连接的获取(JDBCoperation.java)
/数据库连接的获取,driver、url的格式同样可以参考各种数据库对应的jar包,驱动类名和url格式
private static Connection getConn(){
String dbName = "gomoku";
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/"+dbName;
String password = "root";
String userName = "root";
Connection conn = null;
try{
Class.forName(driver);//classLoader加载对应驱动
conn = (Connection) DriverManager.getConnection(url,userName,password);
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return conn;
}
private static Connection getConn(){
String dbName = "gomoku";
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/"+dbName;
String password = "root";
String userName = "root";
Connection conn = null;
try{
Class.forName(driver);//classLoader加载对应驱动
conn = (Connection) DriverManager.getConnection(url,userName,password);
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return conn;
}
3.insert(JDBCoperation.java)
public static int insertA(Player player){int j = 0;
j = insert(player);
return j;
}
//向数据库insert数据
private static int insert(Player player){
Connection conn = getConn();
int i = 0;
String sql = "insert into t_gomoku (G_Name,G_Pass) values(?,?)";
PreparedStatement pstmt;
try{
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, player.getName());
pstmt.setString(2, player.getpassword());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
return i;
}
4.update(JDBCoperation.java)
public static int updateA(Player player){int j = 0;
j = update(player);
return j;
}
//更新数据库数据update
private static int update(Player player){
Connection conn = getConn();
int i = 0;
String sql = "update t_gomoku set G_Pass='"+player.getpassword() + "'where G_Name='"
+ player.getName() + "'";
PreparedStatement pstmt;
try{
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("result: " + i);
pstmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
return i;
}
5.select(JDBCoperation.java)
public static Integer getAllA(){getAll();
return null;
}
//查询数据库
private static Integer getAll(){
Connection conn = getConn();
String sql = "select * from t_gomoku";
PreparedStatement pstmt;
try{
pstmt = (PreparedStatement) conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
System.out.println("==========================");
while(rs.next()){
for(int i=1; i<=col; i++)
{
System.out.print(rs.getString(i) + "\t");
if((i==2)&(rs.getString(i).length() < 8)){
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("==========================");
}catch(SQLException e){
e.printStackTrace();
}
return null;
}
6.delete(JDBCoperation.java)
public static int deleteA(String name){int j = 0;
j = delete(name);
return j;
}
//DELETE
private static int delete(String name){
Connection conn = getConn();
int i = 0;
String sql = "delete from t_gomoku where G_Name='" + name +"'";
PreparedStatement pstmt;
try{
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("result: " + i);
pstmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
return i;
}
测试函数
package src.hl;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class JDBTest {
public static void main(String[] args) {
JDBCOperation.getAllA();
JDBCOperation.insertA(new Player("廖sir","123"));
JDBCOperation.getAllA();
JDBCOperation.updateA(new Player("廖sir","123457"));
//JDBCOperation.deleteA("廖sir");
JDBCOperation.getAllA();
}
}
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class JDBTest {
public static void main(String[] args) {
JDBCOperation.getAllA();
JDBCOperation.insertA(new Player("廖sir","123"));
JDBCOperation.getAllA();
JDBCOperation.updateA(new Player("廖sir","123457"));
//JDBCOperation.deleteA("廖sir");
JDBCOperation.getAllA();
}
}
效果图: