Java数据库编程

数据库和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;
    }
    
    
}
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值