import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class JdbcTest {
public void getConnection()throws Exception{
//①读取配置文件中4个基本信息
InputStream is = JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//②加载驱动
Class.forName(driverClass);
//③获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
public static void main(String[] args) throws Exception{
new JdbcTest().getConnection();
}
}
不包装的SQL语句执行
prepareStatement插入语句
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
public class JdbcTest {
public static void testInsert()throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
//①读取配置文件中4个基本信息
InputStream is = JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//②加载驱动
Class.forName(driverClass);
//③获取连接
conn = DriverManager.getConnection(url,user,password);
//预编译SQL语句,返回实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";//?占位符
ps = conn.prepareStatement(sql);
//填充占位符
ps.setString(1,"乐正绫");
ps.setString(2,"zhengling@163.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
java.util.Date parse = sdf.parse("2020-08-30");
ps.setDate(3,new Date(parse.getTime()));
//执行SQL操作
//ps.executeUpdate();
ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}finally {
//资源关闭
ps.close();
conn.close();
}
}
public static void main(String[] args) throws Exception{
testInsert();
}
}
封装JDBC数据库连接工具类
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnection()throws Exception{
//①读取配置文件中4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//②加载驱动
Class.forName(driverClass);
//③获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
return conn;
}
public static void closeResource(Connection conn, Statement ps){
try{
if(ps!=null){
ps.close();
}
}catch (Exception exception){
exception.printStackTrace();
}
try{
if(conn!=null){
conn.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
}
}
PreparedStatement修改
import java.sql.Connection;
import java.sql.PreparedStatement;
public class JdbcTest{
//修改customers的一条记录
public void testUpdate(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译SQL语句,返回PreparedStatement实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setString(1,"言和");
ps.setInt(2,1);
//4.执行SQL操作
ps.execute();
} catch (Exception exception) {
exception.printStackTrace();
}finally {
//5.资源的管理
JDBCUtils.closeResource(conn,ps);
}
}
public static void main(String[] args) {
new JdbcTest().testUpdate();
}
}
通用的增删改方法
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
public class JdbcTest{
//通用增删改操作
public void update(String sql,Object...args){//sql中的占位符的个数应该和可变形参的长度相同
Connection conn = null;
PreparedStatement ps = null;
try{
//获取数据库连接
conn = JDBCUtils.getConnection();
//预编译SQL语句返回PreparedStatement实例
ps =conn.prepareStatement(sql);
//填充占位符
for(int i=0;i
ps.setObject(i+1,args[i]);
}
ps.execute();
}catch (Exception ex){
ex.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
//修改customers的一条记录
public void testUpdate(){
}
public static void main(String[] args) {
new JdbcTest().testUpdate();
}
}
查询
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class CustomerForQuery {
public static void testQuery1(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet re = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,2);
//执行
//ps.execute();
//执行并返回结果值
re = ps.executeQuery();
//处理结果集
if(re.next()){//判断结果集的下一条是否有数据,如果有数据返回true并指针下移,如果返回false指针不会下移直接结束
//获取当前这条数据的各个字段的值
int id = re.getInt(1);
String name = re.getString(2);
String email = re.getString(3);
Date birth = re.getDate(4);
//方式一:
// System.out.print("id = "+id);
// System.out.print(" name = "+name);
// System.out.print(" email = "+email);
// System.out.print(" birth = "+birth);
//方式二:
//Object[]data = new Object[]{id,name,email,birth};
//方式三:将数据封装为对象
Costomer cunstomer = new Costomer(id,name,email,birth);
System.out.println(cunstomer);
}
} catch (Exception exception) {
exception.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResource(conn,ps,re);
}
}
public static void main(String[] args) {
testQuery1();
}
}
//关闭资源操作
public static void closeResource(Connection conn, Statement ps, ResultSet rs){
try{
if(ps!=null){
ps.close();
}
}catch (Exception exception){
exception.printStackTrace();
}
try{
if(conn!=null){
conn.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
try{
if(rs!=null){
rs.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
}



