//以Mysql为例
//环境方面,导入connector包
//记得import java.sql.*;
//1.加载驱动
Class.forName("驱动名");
//2.获取连接,mysql默认端口号为3306
Connection ct = DriverManager.getConnection("jdbc:mysql://IP:3306/DB名","用户名","密码");
一、针对Statement
//3.创建Statement,用于执行SQL
Statement sm = ct.createStatement();
//执行CUD操作（增删改）时，调用excuteUpdate()方法，sql为SQL DML语句
sm.excuteUpdate(sql);
//由于该方法可以返回一个int值描述执行是否成功，所以可以用一个int变量存储返回值
int flag = sm.excuteUpdate(sql);
//执行查询操作的话,需要调用excuteQuery()方法，返回的是一个ResultSet值
//创建一个ResultSet变量保存查询结果
ResultSet rs = sm.excuteQuery(sql);
//rs一开始指向的是查到结果的前一行即为空,所以调用next()方法让他指向下一行即结果里的第一行
rs.next();
//此时可以取出结果并输出,假设表中第一个字段是一个Int类型,则调用getInt()方法，参数1表示取出此行的第一个字段的值
int a = rs.getInt(1);
System.out.println(a);
//也可以取出全部结果并输出,假设第一个字段是Int类型id,第二个字段是String类型name(可以直接用字段名代替取出方法里的标识第几列的参数，如下rs.getString("name"));
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString("name");
}
二、针对PreparedStatement
//PreparedStatement相当于是Statement的优化，用法也有一些不同
//创建对象的时候就必须指定sql语句,比如添加一条记录,可以用问号代替各字段值后去设置
PreparedStatement ps = ct.prepareStatement("insert into hero values(?,?)");
//如果是int类型字段，调用setInt(int, int),第一个参数代表对应第几个问号，第二个参数就是要设置的值
ps.setInt(1,1);
ps.setString(2,"宋江");
//然后执行，同样可以返回一个int标识是否执行成功
int flag = ps.executeUpdate();
//如果是查询，和添加类似，查询结果同样用ResultSet变量存储
System.out.println("编号为2或姓名为CC的信息");
ps = ct.prepareStatement("select * from hero where id = ? or name = ?");
ps.setInt(1, 2);
ps.setString(2, "CC");
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
三、以上一系列操作通常需要用try/catch包住，最好在最后用finally来关闭Connection之类的资源。
try{
class.forName("…");
Connection ct = ……;
……
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!=null) rs.close();
if(ps!=null) ps.close();
if(ct!=null) ct.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
三、执行DDL操作(create, drop之类的)要调用execute(),该函数可返回一个bool值标识执行是否成功
//例如创建一个数据库
String sql = "create database xx";
//对于Stetement
bool flag = sm.excute(sql);
//对于PreparedStatement
PreparedStatement ps = ct.prepareStatement(sql);
bool flag = ps.excute();
五、附一份代码(不包含DDL操作)
//封装了一些SQL基本操作
//文件名Excute.java
package DAO;
import java.sql.*;
import java.sql.Statement;
import com.mysql.jdbc.*;
public class Excute {
public int add(String s, Statement sm)
{
int i = 0;
try {
i = sm.executeUpdate(s);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return i;
}
public int del(String s, Statement sm)
{
int i = 0;
try {
i = sm.executeUpdate(s);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return i;
}
public int up(String s, Statement sm)
{
int i = 0;
try {
i = sm.executeUpdate(s);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return i;
}
public ResultSet find(String s, Statement sm, ResultSet rs)
{
try {
rs = sm.executeQuery(s);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
//封装了实体
//文件名Hero.java
package Ey;
public class Hero {
private int id;
private String name;
public Hero()
{
}
public Hero(int id, String name)
{
this.id = id;
this.name = name;
}
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;
}
}
//包test用于针对Statement的测试
//文件名Impl.java,其中添加的操作只能进行一次，第二次运行可能会添加失败,除非先到数据库把那条记录删了
package test;
import java.sql.*;
import DAO.*;
import Ey.*;
public class Impl {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection ct = null;
Statement sm =null;
try {
Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://localhost:3306/conn","root","123456");
//2.Statement
sm = ct.createStatement();
Hero h1 = new Hero(1,"AA");
Excute ex = new Excute();
Hero h2 = new Hero(2,"BB");
Hero h3 = new Hero(3,"CC");
String sql_add = "insert into hero values('"+h3.getId()+"','"+h3.getName()+"')";
// String sql_up = "update hero set name = '"+h1.getName()+"' where id = '"+h1.getId()+"'";
int add = ex.add(sql_add, sm);
// int up = sm.executeUpdate(sql_up);
if(add == 1)
System.out.println("Add OK!");
else
System.out.println("Add Fail!");
ResultSet rs = null;
String sql_find = "select * from hero";
rs = ex.find(sql_find, sm, rs);
int i =1;
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("第"+i+"个人的编号为"+id+"，姓名为"+name);
i++;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
try
{
if(sm != null)
sm.close();
if(ct != null);
ct.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
//文件名Tester.java,专门用来测试Statement查询,可多次运行
package test;
import java.sql.*;
import DAO.*;
import Ey.*;
public class Tester {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection ct = null;
Statement sm = null;
ResultSet rs = null;
Excute ex = new Excute();
try
{
Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://localhost:3306/conn","root","123456");
sm = ct.createStatement();
String sql1 = "select * from hero";
rs = ex.find(sql1, sm, rs);
System.out.println("查询全部信息：");
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString("name");
System.out.println("排名:"+id+"，姓名"+name);
}
System.out.println("查询2号信息：");
String sql2 = "select * from hero where id = '2'";
rs = ex.find(sql2, sm, rs);
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名:"+id+"，姓名"+name);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
try
{
if(sm!=null)
sm.close();
if(ct!=null)
ct.close();
}
catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
//test.prestmt包用于针对PreparedStatement的测试
//文件名PreStmt.java,其中添加操作一般只能进行一次理由同Statement测试包
package test.prestmt;
import DAO.*;
import Ey.*;
import java.sql.*;
public class PreStmt {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://localhost:3306/conn","root","123456");
ps = ct.prepareStatement("select * from hero");
rs = ps.executeQuery();
System.out.println("全员信息");
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
System.out.println("编号为2的信息");
ps = ct.prepareStatement("select * from hero where id = ?");
//setInt的第一个参数代表上面的sql语句中的第几个问号，第二个参数才是实际的条件
ps.setInt(1, 2);
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
System.out.println("编号为2或姓名为CC的信息");
ps = ct.prepareStatement("select * from hero where id = ? or name = ?");
ps.setInt(1, 2);
ps.setString(2, "CC");
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
//添加一条记录
Hero h = new Hero(4,"DD");
ps = ct.prepareStatement("insert into hero values(?,?)");
ps.setInt(1, h.getId());
ps.setString(2, h.getName());
int i = ps.executeUpdate();
if(i==1)
{
System.out.println("Add OK!");
ps = ct.prepareStatement("select * from hero where id = 4");
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
}
else System.out.println("Add Fail!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try
{
if(rs!=null) rs.close();
if(ps!=null) ps.close();
if(ct!=null) ct.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
//文件名Tester.java专门用于测试PreparedStatement的查询
package test.prestmt;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Tester {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://localhost:3306/conn","root","123456");
ps = ct.prepareStatement("select * from hero");
rs = ps.executeQuery();
System.out.println("全员信息");
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
System.out.println("编号为2的信息");
ps = ct.prepareStatement("select * from hero where id = ?");
//setInt的第一个参数代表上面的sql语句中的第几个问号，第二个参数才是实际的条件
ps.setInt(1, 2);
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
System.out.println("编号为2或姓名为CC的信息");
ps = ct.prepareStatement("select * from hero where id = ? or name = ?");
ps.setInt(1, 2);
ps.setString(2, "CC");
rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("排名："+id+"，姓名："+name);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!=null) rs.close();
if(ps!=null) ps.close();
if(ct!=null) ct.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
