package com.tedu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBC快速入门案例
*
* @author Administrator
*
*/
public class JDBCDemo1 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
// 1.注册数据库驱动(让JDBC程序管理mysql的jar包)
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接()
// Connection conn =
// DriverManager.getConnection("jdbc:mysql://localhost:3306/jt_db","root","");
conn = DriverManager.getConnection("jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf-8", "root", "");
// 3.获取传输器
stat = conn.createStatement();
// 4.声明SQL并发送SQL到数据库执行,返回执行结果
String sql = "select * from account";
rs = stat.executeQuery(sql);
// 5.处理结果
System.out.println(rs.next());
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id + ":" + name + ":" + money);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询失败");
} finally {
// 6.释放资源(越晚获取的越先关闭)
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
}
查询用的是executeQuery(sql) 增删改用的是executeUpdate(sql)
使用c3p0连接池,导入c3p0的jar包,要注意c3p0-config.xml文件的配置 ,如下:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">
com.mysql.jdbc.Driver
</property>
<property name="jdbcUrl">
jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf-8
</property>
<property name="user">
root
</property>
<property name="password">
</property>
</default-config>
</c3p0-config>
package com.tedu;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* JDBC快速入门案例
*
* @author Administrator
*
*/
public class Testc3p0 {
@SuppressWarnings({ "unused", "null" })
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
//0.创建一个连接池对象
ComboPooledDataSource pool =new ComboPooledDataSource();
try {
// //配置连接数据库的基本信息
// pool.setDriverClass("com.mysql.jdbc.Driver");
// pool.setJdbcUrl("jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf8");
// pool.setUser("root");
// pool.setPassword("");
//1.从连接池中获取一个连接对象
conn = pool.getConnection();
// 3.获取传输器
stat = conn.createStatement();
// 4.声明SQL并发送SQL到数据库执行,返回执行结果
String sql = "select * from account";
rs = stat.executeQuery(sql);
// 5.处理结果
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id + ":" + name + ":" + money);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询失败");
} finally {
// 6.释放资源(越晚获取的越先关闭)
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if (conn != null) {
try {
//如果conn是自己创建的,就是原生的Connection,没有被改造,调用close就是关闭连接,
//如果conn是从连接池中获取的连接对象,这个连接已经被改造过了,调用close方法就是将连接还给连接池
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
}
PreparedStatement 可以有效的防止SQL注入,例子:
public static Connection getConn() throws Exception{
//1.注册驱动
Class.forName( "com.mysql.jdbc.Driver" );
//2.获取连接
Connection conn = DriverManager.getConnection(
"jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf-8",
"root",
"");
return conn;
}
public static void close(Connection conn,
Statement stat, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
rs = null;
}
}
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
stat = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
conn = null;
}
}
}
private static void loginByPS(String username, String pwd) {
Connection conn =null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=JDBCUtil.getConn();
String sql="select * from user where username=? and password=?";
ps=conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, pwd);
rs=ps.executeQuery();
if(rs.next())
{
System.out.println("登录成功!!!");
}else{
System.out.println("用户名或密码不正确");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("登录失败!!!");
}finally{
//释放资源
JDBCUtil.close(conn, ps, rs);
}
}