数据库和SQL
JDBC
import java.sql.*;
public class SelectTest {
public static void main(String[] args){
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
return;
}
String url="jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
import java.sql.*;
public class UpdateTest {
public static void main(String[] args){
executeUpdate("update t_book set price = 300 where bookid = 1");
executeUpdate("insert into t_book(bookid, bookname, price) values(4, '编译原理', 90)");
executeUpdate("delete from t_book where id = 4");
}
public static void executeUpdate(String sql)
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句
int result = stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertTest {
public static void main(String[] a)
{
//concatInsertBook();
//unsafeConcatInsertBook();
//safeInsertBook();
batchInsertBook();
}
public static void concatInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java";
int price = 50;
//values(1, 'Effective Java', 50)
String sql = "insert into t_book(bookid,bookname,price) values("
+ bookid + ", '" + bookName + "', " + price + ")";
int result = stmt.executeUpdate(sql);
stmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
public static void unsafeConcatInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
int price = 50;
//values(1, 'Effective Java', 50)
String sql = "insert into t_book(bookid,bookname,price) values("
+ bookid + ", '" + bookName + "', " + price + ");";
System.out.println(sql);
int result = stmt.executeUpdate(sql);
stmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
public static void safeInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
int price = 50;
//values(1, 'Effective Java', 50)
pstmt.setInt(1, bookid);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
int result = pstmt.executeUpdate();
pstmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
public static void batchInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
String bookName = "aaaaaaaaaaaaaaaa";
int price = 50;
//values(1, 'Effective Java', 50)
for(int i=200;i<210;i++)
{
pstmt.setInt(1, i);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
import java.sql.*;
public class ResultSetMetaDataTest {
public static void main(String[] args){
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
return;
}
String url="jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for(int i=1;i<=cols;i++)
{
System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
}
rs.close();
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
public class TransactionTest {
public static void main(String[] args) throws Exception {
// 构建Java和数据库之间的桥梁介质
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
} catch (ClassNotFoundException e1) {
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url = "jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
// 构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
conn.setAutoCommit(false);
insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");
insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");
insertBook(conn, "insert into t_book values(103, 'cccc', 10)");
Savepoint phase1 = conn.setSavepoint(); //设置一个保存点
insertBook(conn, "insert into t_book values(104, 'cccc', 10)");
insertBook(conn, "insert into t_book values(105, 'cccc', 10)");
conn.rollback(phase1); //回滚到phase1保存点，即上面2行无效
conn.commit();
System.out.println("操作成功");
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
if (null != conn) {
conn.close();
}
}
}
public static void insertBook(Connection conn, String sql) {
try {
// 构建数据库执行者
Statement stmt = conn.createStatement();
//System.out.print("创建Statement成功！");
// 执行SQL语句
int result = stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
数据库连接池
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory1 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass( "com.mysql.jdbc.Driver" );
dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
dataSource.setUser("root");
dataSource.setPassword("123456");
// the settings below are optional -- c3p0 can work with defaults
dataSource.setMinPoolSize(5);
dataSource.setAcquireIncrement(5);
dataSource.setMaxPoolSize(20);
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory2 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
//dataSource 自动加载c3p0-config.xml文件
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.sql.Connection;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidFactory1 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
dataSource.setInitialSize(5);
dataSource.setMinIdle(1);
dataSource.setMaxActive(10);
// 启用监控统计功能 dataSource.setFilters("stat");//
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidFactory2 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
Properties properties = new Properties();
InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(in);
dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
in.close();
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.sql.*;
public class SelectTest {
public static void main(String[] args){
Connection conn = null;
try {
//从c3p0获取
//conn = C3p0Factory1.getConnection();
//conn = C3p0Factory2.getConnection();
//从Druid获取
//conn = DruidFactory1.getConnection();
conn = DruidFactory2.getConnection();
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (Exception e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
作业
假设数据库有一张表t_mail (id, from, to, subject, content), 里面存储着具体的邮件发件人、收件人、标题和内容。采用Druid连接池，读取id为1的记录，并基于Java Mail将该邮件发送出来。
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
* 假设数据库有一张表t_mail (id, from, to, subject, content),
* 里面存储着具体的邮件发件人、收件人、标题和内容。
* 采用Druid连接池，读取id为1的记录，并基于Java Mail将该邮件发送出来。
*/
public class DBMailSend {
public static void main(String[] args) {
Connection conn=null;
String from;
String to;
String subject;
String content;
String password;
String smtpServer;
try {
//从Druid获取
conn=DruidFactory.getConnection();
System.out.println("连接池构建成功");
//构造数据库执行者
Statement stmt=conn.createStatement();
System.out.println("获取连接成功");
//执行SQL语句并返回结果到ResultSet
ResultSet rs=stmt.executeQuery("select id,mfrom,mto,msubject,content,password,smtpServer from t_mail order by id");
System.out.println("获取数据成功");
//开始遍历ResultSet数据
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6)+","+rs.getString(7));
if(rs.getInt(1)==1) {
from=rs.getString(2);
to=rs.getString(3);
subject=rs.getString(4);
content=rs.getString(5);
password=rs.getString(6);
smtpServer=rs.getString(7);
MailClientSend client=new MailClientSend(from,password,smtpServer);
client.init();
client.sendMessage(from, to, subject, content);
System.out.println("发送邮件成功");
}
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(null!=null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
import java.sql.Connection;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidFactory {
private static DruidDataSource dataSource=null;
public static void init()throws Exception{
dataSource=new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
dataSource.setMinIdle(1);
dataSource.setMaxActive(10);
//启动监控统计功能 dataSource.setFilters("stat");
}
public static Connection getConnection()throws Exception{
if(null==dataSource) {
init();
}
return dataSource.getConnection();
}
}
import java.util.Properties;
import javax.mail.Authenticator;
import javax.mail.Message;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
public class MailClientSend {
private Session session;
private Transport transport;
private String username;
private String password;
//private String smtpServer="smtp.qq.com";
private String smtpServer;
public MailClientSend(String username, String password, String smtpServer) {
super();
this.username = username;
this.password = password;
this.smtpServer = smtpServer;
}
public void init()throws Exception{
//设置属性
Properties props=new Properties();
props.put("mail.transport.protocol", "smtp");
props.put("mail.smtp.class", "com.sun.mail.smtp.SMTPTransport");
props.put("mail.smtp.host", smtpServer);//设置发送邮件服务器
props.put("mail.smtp,port","25");
props.put("mail.smtp.auth", "true");//SMTP服务器需要身份验证
//创建Session对象
session=Session.getInstance(props,new Authenticator() {//验证账户
public PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,password);
}
});
//session.setDebug(true);//输出跟踪日志
//创建Transport对象
transport=session.getTransport();
}
public void sendMessage(String from, String to, String subject, String body)throws Exception{
//创建一个邮件
TextMessage tmsg=new TextMessage(from,to,subject,body);
Message msg=tmsg.generate();
transport.connect();
transport.sendMessage(msg, msg.getAllRecipients());
//打印结果
System.out.println("邮件已经发送成功");
}
public void close()throws Exception{
transport.close();
}
}
import java.util.Date;
import java.util.Properties;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
public class TextMessage {
private String from;//发件人地址
private String to;//收件人地址
private String subject;//标题
private String body;//正文
public TextMessage(String from, String to, String subject, String body) {
super();
this.from = from;
this.to = to;
this.subject = subject;
this.body = body;
}
public MimeMessage generate()throws Exception{
//创建Session实例对象
Session session=Session.getDefaultInstance(new Properties());
//创建MimeMessage实例对象
MimeMessage message=new MimeMessage(session);
//设置发件人
message.setFrom(new InternetAddress(from));
//设置收件人
message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(to));
//设置发送日期
message.setSentDate(new Date());
//设置邮件主题
message.setSubject(subject);
//设置纯文本文件内容的邮件正文
message.setText(body);
//保存并生成最终的邮件内容
message.saveChanges();
//把MimeMessage对象中的内容写入到文件中
//msg.writeTo(new FileOutputStream("e:/test.eml));
return message;
}
}
2671
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
