java 连接数据库首先要导入一个jar包
我们来了解一下什么是jdbc
其实JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java AP
读下面代码需要大概了解Statement常用的方法:
ResultSet executeQuery(String sql):执行SQL查询并且获取ResultSet对象
Int executeUpdate(String sql):可以执行插入、删除、更新等操作,返回值是执行该操作所影响的行数
Boolean execute(String sql):可以执行任意SQL语句,然后获得一个布尔值,表示是否返回ResultSet
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * *@desc JDBC的基本操作 *@author anthor *@time **/ public class Test { public static void main(String[] args) throws Exception { String sql=" select * from user "; //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.指定用户名和密码 Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/table?useUnicode=true&characterEncoding=utf-8", "root","root" ); if (!conn.isClosed()) //3.获取连接 { System.out.println("连接成功了"); //4.创建Statement对象 Statement stmt = conn.createStatement(); //5.执行sql语句 ResultSet rs = stmt.executeQuery(sql); //6.展示结果集 while (rs.next()) { System.out.println(rs.getString("id")+"\t"+rs.getString("name")); } //7.关闭对象 rs.close(); stmt.close(); conn.close(); } }
这只是一个简单的查询的操作
JDBC的Dao模式 实质上就是在上面代码中 去重构代码 从而达到
1.隔离业务逻辑代码和数据访问代码
2.隔离不同数据库的实现
的作用
Dao模式: 接口 实现类 测试类
下面代码是实现基本Dao模式的接口
import java.sql.SQLException;
import java.util.List;
import java.util.Map;public interface IDao {
public List<Map<String, Object>> executeQueryForList(String sql) throws Exception, SQLException;
public Map<String, Object> executeQueryForMap(String sql) throws Exception, SQLException;public int executeQueryForCount(String sql) throws ClassNotFoundException, SQLException;
public int executeUpdate(String sql) throws Exception, SQLException;
}
实现类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.Types;public class Dao implements IDao{
public static String diverName="com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://localhost:3306/tab?useUnicode=true&characterEncoding=UTF-8";
public static String userName="root";
public static String password="root";
/**
* @return 1.获取连接
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException,SQLException {
Class.forName(diverName);//加载
//指定用户名和密码
Connection conn = DriverManager.getConnection(url, userName,password);
return conn;
}
/**
* @deac 2.查询数据
* @param conn
* @param sql
* @throws SQLException
* @throws ClassNotFoundException
*/
public List<Map<String, Object>> executeQueryForList(String sql) throws SQLException, ClassNotFoundException {
System.err.println(sql);
Connection conn = getConnection();
//创建Statement对象
Statement stmt = conn.createStatement();
//执行sql语句
ResultSet rs = stmt.executeQuery(sql);
List<Map<String, Object>> list = rsToList(rs);
closeConnection(conn, stmt, rs);//关闭
return list;
}
/**
* @desc 2 .将rs转换为List<map>
* @param rs
* @return
* @throws SQLException
*/
private List<Map<String, Object>> rsToList(ResultSet rs) throws SQLException{
//初始化list对象
List<Map<String, Object>> row=new ArrayList<Map<String,Object>>();
while (rs.next()) {//判断
//创建map容器
Map<String, Object> col =new HashMap<String, Object>();
for (int i = 1; i <=rs.getMetaData().getColumnCount(); i++) {
//判断数据类型
switch (rs.getMetaData().getColumnType(i)) {
case Types.VARCHAR:
col.put(rs.getMetaData().getColumnName(i), rs.getString(i));
break;
case Types.INTEGER:
col.put(rs.getMetaData().getColumnName(i), rs.getInt(i));
break;
default:
break;
}
}
//添加map数据
row.add(col);
}
return row;
}
/**
* @desc 3.查询操作:获取一条数据
* @param sql
* @return
* @throws Exception
* @throws SQLException
*/
public Map<String, Object> executeQueryForMap(String sql) throws Exception, SQLException
{
System.err.println(sql);
Connection conn = Dao.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Map<String, Object>> list=this.rsToList(rs);
this.closeConnection(conn, stmt, rs);
if (!list.isEmpty())
{
return list.get(0);
}
return null;
}
/**
* @desc 4.查询操作:获取总记录数
* @param sql
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public int executeQueryForCount(String sql) throws ClassNotFoundException, SQLException{
System.err.println(sql);
//链接数据库
Connection conn = Dao.getConnection();
//创建Statement对象
Statement stmt = conn.createStatement();
//执行sql语句
ResultSet rs = stmt.executeQuery(sql);
int count = 0;
if (rs.next()) {
count = count+rs.getInt(1);
}
this.closeConnection(conn, stmt, rs);
return count;
}
/**
* @desc 5.执行添加,修改,删除操作
* @param sql
* @return
* @throws Exception
* @throws SQLException
*/
public int executeUpdate(String sql) throws Exception, SQLException
{
System.err.println(sql);
Connection conn = Dao.getConnection();//连接
Statement stmt = conn.createStatement();//创建
int count = stmt.executeUpdate(sql);//执行
this.releaseConnection(conn, stmt);//关闭
return count;
}
/**
* @desc 6.关闭连接:关闭两个对象
* @param conn
* @param stmt
* @throws SQLException
*/
private void releaseConnection(Connection conn, Statement stmt) throws SQLException {
try {
//判断是否为空
if (stmt!=null)
{
stmt.close();
}
if (conn!=null)
{
conn.close();
}
}
catch (SQLException e)
{
throw new SQLException("数据库关闭异常");
}
}
/**
* @desc 7.关闭连接
* @param conn
* @param stmt
* @param rs
* @throws SQLException
*/
public void closeConnection(Connection conn, Statement stmt,
ResultSet rs) throws SQLException {
try {
if (rs!=null) {
rs.close();
}
if (stmt!=null) {
stmt.close();
}
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
throw new SQLException("数据关闭异常!");
}
}
}
测试类
public class CeShi {
public static void main(String[] args) throws Exception
{
IDao dao = new Dao();
String sql = " select * from table01 where id='01' ";
List<Map<String, Object>> list = dao.executeQueryForList(sql);
for (Map<String, Object> map : list) {
System.out.println(map);
}
// int count = dao.executeUpdate(sql);
// System.out.println(count!=0?"运行成功!":"运行失败!");
}
}
下面普及一下JDBC的常用的关键字
1.DriverManager:依据数据库的不同,管理JDBC驱动
2.Connection:负责连接数据库并且担任传送数据库的任务
3.Statement:由Connection产生、负责执行SQL语句
4.ResultSet:负责保存Statement执行后所产生的查询结果