Database connection in a TelosysTools template

gistfile1.java

import java.io.File;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcTool {

	private String driverClassName = null ;
	private String driverJarFile = null ;
	
    //-----------------------------------------------------------------------------
    // Specific Class Loader ( Inner class )
    //-----------------------------------------------------------------------------
    private static class MyClassLoader extends URLClassLoader
    {
        public MyClassLoader (URL[] urls, java.lang.ClassLoader parentLoader )
        {
            //--- Call the URLClassLoader constructor
            super(urls, parentLoader);
        }
    }

    public void setDriverClassName(String value) {
    	driverClassName = value ;
    }
    
    public void setDriverJarFile(String value) {
    	driverJarFile = value ;
    }
    
	/**
	 * @param driverClassName
	 * @param driverJarFile
	 * @return
	 */
	public Driver getDriver(String driverClassName, String driverJarFile) {

		if ( driverClassName == null ) {
        	throw new RuntimeException("Cannot get driver: driverClassName is null");
		}
		if ( driverJarFile == null ) {
        	throw new RuntimeException("Cannot get driver: driverJarFile is null");
		}
				
		URL[] urls = new URL[1];
        try
        {
            // urls[n] = new File(sPath).toURL(); // toURL deprecated since Java 5.0
            // The recommended new code ( see JavaDoc )
            URI uri = new File(driverJarFile).toURI();
            urls[0] = uri.toURL();
        } 
        catch (MalformedURLException e)
        {
        	throw new RuntimeException("Cannot convert '" + driverJarFile + "' to URL (MalformedURLException)", e);
        }
        
        ClassLoader parentLoader = ClassLoader.getSystemClassLoader();

		MyClassLoader loader = new MyClassLoader ( urls, parentLoader );
		
		Class<?> c = null ;
		try {
			c = loader.loadClass(driverClassName);
		} catch (ClassNotFoundException e) {
        	throw new RuntimeException("Class not found '" + driverClassName + "' ", e);
		}
		
		Driver driver = null ;
		try {
			driver = (Driver) c.newInstance();
		} catch (InstantiationException e) {
        	throw new RuntimeException("Cannot instanciate driver '" + driverClassName + "' ", e);
		} catch (IllegalAccessException e) {
        	throw new RuntimeException("Illegal access / driver '" + driverClassName + "' ", e);
		}
		
		return driver ;
	}
	
	public Connection getConnection() {
		
		Driver driver = getDriver(this.driverClassName, this.driverJarFile);
		
		Connection connection = null ;
		try {
			Properties prop = new Properties() ;
			prop.put("user",     "root" ) ; 
			prop.put("password", "admin" ) ;			
			connection = driver.connect("jdbc:derby://localhost:1527/bookstore", prop);
		} catch (SQLException e) {
        	throw new RuntimeException("Cannot get connection", e);
		}
		return connection ;
	}	

	public Connection getConnection(String url, String user, String password ) {
		
		Driver driver = getDriver(this.driverClassName, this.driverJarFile);
		
		Connection connection = null ;
		try {
			Properties prop = new Properties() ;
			prop.put("user",     user ) ; 
			prop.put("password", password ) ;			
			connection = driver.connect(url, prop);
		} catch (SQLException e) {
        	throw new RuntimeException("Cannot get connection", e);
		}
		return connection ;
	}	
}

NB : in this example the connection is never closed (do not use it as is)

Usage in the template :

set( $jdbcTool = $loader.newInstance("JdbcTool") )

$jdbcTool.setDriverClassName("org.apache.derby.jdbc.ClientDriver")
$jdbcTool.setDriverJarFile("D:/workspaces/wks43-emn-telosys-tools/tmp-test-telosys/TelosysTools/lib/derbyclient.jar")

set( $jdbcDriver = $jdbcTool.getDriver("org.apache.derby.jdbc.ClientDriver", "D:/workspaces/wks43-emn-telosys-tools/tmp-test-telosys/TelosysTools/lib/derbyclient.jar") )

set( $connection = $jdbcTool.getConnection() )

set( $connection = $jdbcTool.getConnection("jdbc:derby://localhost:1527/bookstore", "root", "admin") )

To see the current class path :

    ClassLoader cl = ClassLoader.getSystemClassLoader();
    URL[] urls = ((URLClassLoader)cl).getURLs();
    for(URL url: urls){
        System.out.println(url.getFile());
    }

Hi,
I need to have have more attributes for table and column
(by example, having for each column : the place in form, the tab in form, etc)

for that i plan to access another table (by example telosyscolext) having

• tablename example : users
• columname example : name
• attribute1 example : 1
• attribute2 example : tab_1

to do that, i expect to use the present example

unfortunately, i don't find the correct classpath to put the generated jar.
i tried to put it in the telosys/lib (same place than the driver) but this don't work

i saw the code for the current class path example, but i don't see where to put it

thx for all

Hi,

Your specific classes must be located in the 'classes' folder (in the templates bundle)
or in a jar located in the 'lib' folder (in the templates bundle)

For example :

• TelosysTools/templates/mybundle/classes : mypackage/xx.class

• TelosysTools/templates/mybundle/lib : xx.jar

See the "$loader" object documentation : https://www.telosys.org/templates-doc/objects/loader.html

See also the examples provided in the budle "advanced-templates-samples-T300" :
https://github.com/telosys-templates-v3/advanced-templates-samples-T300

FYI : if you are using a DSL model, the upcoming version will provide "TAGS" to define specific information
A tag is an string starting with "#" with or without information in parenthesis
something like "#attribute1(info)" or "#mytag"
All these tags will be usable in templates
It could be a solution for your specific needs

Hi,
Thx for the click reply. Thats clearly better ;-)
For TAGS, it may be a solution.
best regards