COMPUTER PROGRAMMING 2014 WEEK 11 HOMEWORKS

EX1 ) Temperature and specific heat of CO₂ gas is given in file co2_cv.txt . Create database table CO2 in database temp by using create table CO2 (T float, Cv float, TK float); command. Read data as java input from file, calculate TK=T+273 by using Update

update CO2 set TK=T+273.15;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.io.*;

public class createCO2{

public static void main(String args[]) throws ClassNotFoundException

{

Class.forName("org.sqlite.JDBC");

Connection connection = null;

try {

connection = DriverManager.getConnection("jdbc:sqlite:temp.db");

Statement stmt = null;

ResultSet rs1 = null;

stmt = connection.createStatement();

String s1= "create table CO2 (T float, CV float, TK float );";

if (stmt.execute(s1)) {rs1 = stmt.getResultSet();}

} catch (SQLException ex) {

// handle any errors

System.out.println("SQLException: " + ex.getMessage());

System.out.println("SQLState: " + ex.getSQLState());

System.out.println("VendorError: " + ex.getErrorCode());

}

}

}

import java.io.*;

import java.util.*;

import javax.swing.*;

import javax.swing.table.*;

import java.awt.*;

import java.sql.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

class data

{

public double x[];

public data(double[] xi)

{

int n=xi.length;

x=new double[n];

for(int i=0;i<n;i++) {x[i]=xi[i];}

}

public String toString() {return ""+x[0]+" "+x[1];}

}

public class insertCO2

{

public static double[][] inputdouble(String filename) throws IOException

{

//reading double values from one line of data

BufferedReader fin=new BufferedReader(new FileReader(filename));

double a[][]=inputdouble(fin);

return a;

}

public static double[][] inputdouble(BufferedReader fin) throws IOException

{

ArrayList<data> a=new ArrayList<data>();

int mmax=0;

double b[][];

int n=0,m=0;

String s1="";

try{

while(fin!=null)

{ s1=fin.readLine();

if(s1==null)break;

StringTokenizer token=new StringTokenizer(s1);

m=token.countTokens();

if(m>mmax) mmax=m;

double aa[]=new double[m];

int j=0;

while(token.hasMoreTokens())

{ Double ax=new Double(token.nextToken());

aa[j++]=ax.doubleValue();

}

data vx=new data(aa);

a.add(vx);

n++;

}

} catch(EOFException e_eof)

{

//close ffile

try{fin.close();

}

catch(NullPointerException e) {}

catch(IOException e)

{

System.err.println("Error Closing File\n"+e.toString());

System.exit(1);

}

} //End of EOFException

b=new double[n][mmax];

int k=0;

Iterator i=a.iterator();

while(i.hasNext()) {data vx=(data)i.next();b[k++]=vx.x;}

return b;

}

public static String toString(double a[][])

{

String s="";

for(int j=0;j<a.length;j++)

{s+="T["+j+"] = "+a[j][0]+" Cv["+j+"] = "+a[j][1]+" 0.0\n";}

s+="\n";

return s;

}

public static void main(String args[]) throws IOException,ClassNotFoundException

{ Class.forName("org.sqlite.JDBC");

Connection conn = null;

try {

String s1="co2_cv.txt";

File f=new File(s1);

BufferedReader fin=new BufferedReader(new FileReader(f));

double number[][]=inputdouble(fin);

conn =DriverManager.getConnection("jdbc:sqlite:temp.db");

Statement stmt = null;

ResultSet rs = null;

stmt = conn.createStatement();

String s2="insert into CO2 values(";

String s3="";

for(int i=0;i<number.length;i++)

{

s3=s2+number[i][0]+","+number[i][1]+", 0.0);";

stmt.executeUpdate(s3);

}

JOptionPane.showMessageDialog(null,toString(number),"data read into the database CO2",JOptionPane.PLAIN_MESSAGE);

System.exit(0);

}

catch (SQLException ex) {

System.out.println("SQLException: " + ex.getMessage());

System.out.println("SQLState: " + ex.getSQLState());

System.out.println("VendorError: " + ex.getErrorCode());

}

}

}

EX 2) : Create table sinx with 2 column x and y. Enter x column values . 0.1 0.2 .03 0.4 0.5 , enter all y values as 0 . Change y values to sin(x) by using update statement. Write out the results.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.io.*;

public class sinx{

public static void main(String args[]) throws ClassNotFoundException

{

Class.forName("org.sqlite.JDBC");

Connection connection = null;

try {

connection = DriverManager.getConnection("jdbc:sqlite:temp.db");

Statement stmt = null;

ResultSet rs1 = null;

stmt = connection.createStatement();

stmt.setQueryTimeout(30);

stmt.executeUpdate("drop table if exists sinx");

String s1= "create table sinx (x double, y double);";

stmt.executeUpdate(s1);

double x[]={0.1, 0.2, 0.3, 0.4, 0.5 };

for(int i=0;i<x.length;i++)

{ s1="insert into sinx values("+x[i]+","+0+");";

System.out.println(s1);

stmt.executeUpdate(s1);

}

s1="update sinx set y=sin(x);";

stmt.executeUpdate(s1);

ResultSet rs = stmt.executeQuery("select * from sinx");

while(rs.next())

{

// read the result set

System.out.println("x = " + rs.getString("x")+" y = " + rs.getString("y"));

}

}catch(SQLException e)

{

// if the error message is "out of memory",

// it probably means no database file is found

System.err.println(e.getMessage());

}

finally

{

try

{

if(connection != null)

connection.close();

}

catch(SQLException e)

{

// connection close failed.

System.err.println(e);

}

}

}

}

EX3) : Create table student with data fields name String, surname String ,grade1 double, grade 2 double, result double Input the following data

‘Ali’ ‘Çiçek’ 24 58

‘Veli’ ‘Durmuş’ 49 73

‘Hasan’ ‘Yücel’ 72 68

‘Mehmet’ ‘Demir’ 63 55

Calculate result as 40% grade1 and 60% grade2 and write down into database result column. Print out the results.

EX4) Temperature and specific heat data of CO₂ gas is entered to CO2 table in exercise 1 . Read the data, from database temp.CO2, calculate the avarage temperatures and avarage CV value and print out the results.

import java.sql.*;

import java.io.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class averageH8{

public static void main(String args[]) throws IOException,ClassNotFoundException

{

try {

Connection conn =DriverManager.getConnection("jdbc:sqlite:temp.db");

Statement stmt = null;

ResultSet rs1 = null;

stmt = conn.createStatement();

String query1="select AVG(CV) as Tavg from CO2";;

if (stmt.execute(query1)) {rs1 = stmt.getResultSet();}

while (rs1.next()) {

double i= rs1.getDouble("Tavg");

System.out.println("average of CV = "+i);

}

} catch (SQLException ex) {

// handle any errors

System.out.println("SQLException: " + ex.getMessage());

System.out.println("SQLState: " + ex.getSQLState());

System.out.println("VendorError: " + ex.getErrorCode());

}

}

}

import java.sql.*;

import java.io.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class average1H8{

public static void main(String args[]) throws IOException,ClassNotFoundException

{

try {

Connection conn =DriverManager.getConnection("jdbc:sqlite:temp.db");

Statement stmt = null;

ResultSet rs1 = null;

stmt = conn.createStatement();

String query1="select CV from CO2";;

if (stmt.execute(query1)) {rs1 = stmt.getResultSet();}

int j=0;

double sum=0;

while (rs1.next()) {

double number= rs1.getDouble("CV");

sum+=number;

j++;

}

double average=sum/j;

System.out.println("average : "+average);

} catch (SQLException ex) {

// handle any errors

System.out.println("SQLException: " + ex.getMessage());

System.out.println("SQLState: " + ex.getSQLState());

System.out.println("VendorError: " + ex.getErrorCode());

}

}

}