java与数据库连接需要准备对应的连接jar包，并导入项目
Demo1 :查询dept表中的所有数据deptno,dname,loc，并在控制台显示
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 查询dept表中的所有数据deptno,dname,loc，并在控制台显示
* @author Administrator
*/
public class JDBCDemo1 {
public static void main(String[] args) {
// 将声明放在外面，以便finally中关闭
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.加载数据库驱动程序（需要把驱动加载到方法区），现在可以省略不写
// Class.forName("com.mysql.jdbc.Driver");
// 2.利用驱动管理器DriverManager获取数据库连接Connection
// localhost 本地，如果需要连接他人数据库，修改成对方的ip
// 3306 mysql默认端口号
// jdbc数据库名称（不是连接名称）
// useUnicode=true&characterEncoding=utf8 支持中文
String url = "jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
// 3.获取SQL语句对象
String sql = "select * from dept";
ps = conn.prepareStatement(sql);
// 4.执行语句等到结果集
rs = ps.executeQuery();
while(rs.next()) {
// 获取编号
int deptno = rs.getInt("deptno");
// 获取部门名称
String dname = rs.getString("dname");
// 获取工作地点
String loc = rs.getString("loc");
System.out.println(deptno+","+dname+","+loc);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭回收资源
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Demo2:查询emp表中empno,ename,sal,hiredate在控制台显示
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 查询emp表中empno,ename,sal,hiredate在控制台显示
*
* @author Administrator
*
*/
public class JDBCDemo2 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String url = "jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
// 3.获取SQL语句对象
// String sql = "select empno,ename,sal,date_format(hiredate,'%Y-%m-%d %H:%i:%s') hiredate from emp";
String sql = "select empno,ename,sal,hiredate from emp";
ps = conn.prepareStatement(sql);
// 4.执行语句等到结果集
rs = ps.executeQuery();
while (rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date hiredate = rs.getDate("hiredate");
// String hiredate = rs.getString("hiredate");
System.out.println(empno+","+ename+","+sal+","+(hiredate == null?"null":sdf.format(hiredate)));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭回收资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Demo3:模糊查询
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
/**
* 查询emp表中姓名中包含S的员工姓名，工资
* @author Administrator
*/
public class JDBCDemo4 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Scanner scan = new Scanner(System.in);
System.out.println("请输入:");
String str = scan.next();
try {
String url = "jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
// 3.获取SQL语句对象
String sql = "select ename,sal from emp where binary ename like ?";
ps = conn.prepareStatement(sql);
// 对占位符问号进行赋值
ps.setString(1, "%"+str+"%");
// 4.执行语句等到结果集
rs = ps.executeQuery();
while (rs.next()) {
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(ename+","+sal);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭回收资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Demo4:查询30部门，1985年以前入职的员工姓名，工资，部门名称，工作地点
/**
* 查询30部门，1985年以前入职的员工姓名，工资，部门名称，工作地点
* @author Administrator
*
*/
public class JDBCDemo5 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String url = "jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
ps = conn.prepareStatement("select ename,sal,dname,loc \r\n" +
"from emp e\r\n" +
"join dept d \r\n" +
"on e.deptno = d.deptno \r\n" +
"where d.deptno = ?\r\n" +
"and hiredate < ?");
ps.setInt(1, 30);
ps.setString(2, "1985-1-1");
rs = ps.executeQuery();
while(rs.next()) {
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
System.out.println(ename+","+sal+","+dname+","+loc);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
网友评论